Miscellaneous Character Functions

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


The following sections show a few other character functions worth mentioning. Once again, these are functions that are fairly common among major implementations .

Finding a Value's Length

The LENGTH function is a common function used to find the length of a string, number, date, or expression in bytes. The syntax is

 graphics/syntax_icon.gif LENGTH(CHARACTER STRING) 

Example

Meaning

SELECT LENGTH EMPLOYEE_TBL

This SQL statement (LAST_NAME) returns the length of the From last name for each employee.

 graphics/input_icon.gif  SELECT PROD_DESC, LENGTH(PROD_DESC)   FROM PRODUCTS_TBL;  graphics/output_icon.gif PROD_DESC                                LENGTH(PROD_DESC) ------------------------                 ----------------- WITCHES COSTUME                          15 PLASTIC PUMPKIN 18 INCH                  23 FALSE PARAFFIN TEETH                     19 LIGHTED LANTERNS                         16 ASSORTED COSTUMES                        17 CANDY CORN                               10 PUMPKIN CANDY                            13 PLASTIC SPIDERS                          15 ASSORTED MASKS                           14 KEY CHAIN                                 9 OAK BOOKSHELF                            13 11 rows selected. 

NVL (NULL Value)

The NVL function is used to return data from one expression if another expression is NULL. NVL can be used with most data types; however, the value and the substitute must be the same data type. The syntax is

 graphics/syntax_icon.gif NVL('VALUE', 'SUBSTITUTION') 

Example

Meaning

SELECT NVL(SALARY, '00000') FROM EMPLOYEE_PAY_TBL;

This SQL statement finds NULL values and substitutes 00000 for any NULL values.

 graphics/mysql_icon.gif graphics/input_icon.gif  SELECT PAGER, NVL(PAGER,9999999999)   FROM EMPLOYEE_TBL;  graphics/output_icon.gif PAGER      NVL(PAGER, ---------- ----------            9999999999            9999999999 3175709980 3175709980 8887345678 8887345678            9999999999            9999999999 6 rows selected. 

Only NULL values were represented as 9999999999.

LPAD

LPAD (left pad) is used to add characters or spaces to the left of a string. The syntax is

 graphics/syntax_icon.gif LPAD(CHARACTER SET) 

The following example pads periods to the left of each product description, totaling 30 characters between the actual value and padded periods.

 graphics/input_icon.gif  SELECT LPAD(PROD_DESC,30,'.') PRODUCT   FROM PRODUCTS_TBL;  graphics/output_icon.gif PRODUCT ------------------------------ ...............WITCHES COSTUME .......PLASTIC PUMPKIN 18 INCH ..........FALSE PARAFFIN TEETH ..............LIGHTED LANTERNS .............ASSORTED COSTUMES ....................CANDY CORN .................PUMPKIN CANDY ...............PLASTIC SPIDERS ................ASSORTED MASKS .....................KEY CHAIN ........ ........OAK BOOKSHELF 11 rows selected. 

RPAD

The RPAD (right pad) is used to add characters or spaces to the right of a string. The syntax is

 graphics/syntax_icon.gif RPAD(CHARACTER SET) 

The following example pads periods to the right of each product description, totaling 30 characters between the actual value and padded periods.

 graphics/input_icon.gif  SELECT RPAD(PROD_DESC,30,'.') PRODUCT   FROM PRODUCTS_TBL;  graphics/output_icon.gif PRODUCT ------------------------------ WITCHES COSTUME............... PLASTIC PUMPKIN 18 INCH....... FALSE PARAFFIN TEETH.......... LIGHTED LANTERNS.............. ASSORTED COSTUMES............. CANDY CORN.................... PUMPKIN CANDY................. PLASTIC SPIDERS............... ASSORTED MASKS................ KEY CHAIN........ ........ ... OAK BOOKSHELF........ ........ 11 rows selected. 

ASCII

The ASCII function is used to return the ASCII (American Standard Code for Information Interchange) representation of the leftmost character of a string. The syntax is

 graphics/syntax_icon.gif ASCII(CHARACTER SET) 

Examples:

ASCII('A') returns 65

ASCII('B') returns 66

ASCII('C') returns 67

ASCII('a') returns 97

For more information, refer to the ASCII chart in Appendix B,"ASCII Table."


Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net