Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition

By Ronald R. Plew, Ryan K. Stephens

Table of Contents
Hour 11.  Restructuring the Appearance of Data

Most functions can be combined in a 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 .

When embedding functions within functions in a SQL statement, remember that the innermost function is resolved first, and then each function is subsequently resolved from the inside out.


Team-Fly    
Top
 

Категории