Discovering SQL: A Hands-On Guide for Beginners

BUILDING CHARACTER

SQL functions excel at manipulating strings; for example, changing letter case, changing alignment, finding ASCII codes, extracting substrings, and so on. Usually, but not always, the output of such functions in RDBMS implementations is a string (even though SQL Standard mandates it to be always a string).

What can they do for us? Let's start with concatenation. The following query would return all records from BOOKS table concatenating values in BK_TITLE and BK_ISBN columns.

SELECT CONCAT(bk_title, bk_ISBN) FROM books;

There is a rather serious limitation of the CONCAT function. It can only accept two parameters, which means that only two fields can be concatenated at a time. To concatenate more strings together, you have to use some workarounds, such as staggering the functions or using more intuitive concatenation operators. Here is an example using the former trick:

SELECT CONCAT(CONCAT(bk_title, ‘,’),bk_ISBN) FROM books;

The output of the inner CONCAT function serves as input for the outer CONCAT function, and the result is the list of titles and ISBN numbers separated by a comma. To alleviate burdens of this somewhat unintuitive syntax, the RDBMS came up with an alternative use of the operator, and some (such as MySQL) allow more than two arguments into their CONCAT functions. Oracle, IBM DB2, and PostgreSQL use || (two vertical lines) as their concatenation operator, while Microsoft SQL Server uses a plus sign (+). The following syntax will be valid ...