The Concept of Combining Character Functions

Most functions can be combined in a single SQL statement. SQL would be far too limited if function combinations were not allowed. The following examples show how some functions can be combined with one another in a query:

						SELECT LAST_NAME || ', ' || FIRST_NAME NAME,
						SUBSTR(EMP_ID,1,3) || '-' ||
						SUBSTR(EMP_ID,4,2) || '-' ||
						SUBSTR(EMP_ID,6,4) ID
						FROM EMPLOYEE_TBL;
					

NAME               ID
------------------ -----------
STEPHENS, TINA     311-54-9902
PLEW, LINDA        442-34-6889
GLASS, BRANDON     213-76-4555
GLASS, JACOB       313-78-2439
WALLACE, MARIAH    220-98-4332
SPURGEON, TIFFANY  443-67-9012

6 rows selected.

The following example combines two functions in the query (concatenation with substring). By pulling the EMP_ID column apart into three pieces, you can concatenate those pieces with dashes to render a readable Social Security number.

						SELECT SUM(LENGTH(LAST_NAME) + LENGTH(FIRST_NAME)) TOTAL
						FROM EMPLOYEE_TBL;
					

     TOTAL
----------
        71

1 row selected.

This example uses the LENGTH function and the arithmetic operator (+) to add the length of the first name to the length of the last name for each column; the SUM function then finds the total length of all first and last names.