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 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. | SELECT PROD_DESC, LENGTH(PROD_DESC) FROM PRODUCTS_TBL; 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 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. | SELECT PAGER, NVL(PAGER,9999999999) FROM EMPLOYEE_TBL; 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 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. SELECT LPAD(PROD_DESC,30,'.') PRODUCT FROM PRODUCTS_TBL; 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 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. SELECT RPAD(PROD_DESC,30,'.') PRODUCT FROM PRODUCTS_TBL; 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 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." |