Sams Teach Yourself SQL in 24 Hours

By Ron Plew and Ryan Stephens

• Table of Contents
  • Copyright
  • About the Authors
  • Acknowledgments
  • Tell Us What You Think!
  • Introduction
  • Part I: A SQL Concepts Overview
  • Hour 1. Welcome to the World of SQL
  • Part II: Building Your Database
  • Hour 2. Defining Data Structures
  • Hour 3. Managing Database Objects
  • Hour 4. The Normalization Process
  • Hour 5. Manipulating Data
  • Hour 6. Managing Database Transactions
  • Part III: Getting Effective Results from Queries
  • Hour 7. Introduction to the Database Query
  • Hour 8. Using Operators to Categorize Data
  • Hour 9. Summarizing Data Results from a Query
  • Hour 10. Sorting and Grouping Data
  • Hour 11. Restructuring the Appearance of Data
    • The Concepts of ANSI Character Functions
    • Various Common Character Functions
    • Miscellaneous Character Functions
    • Mathematical Functions
    • Conversion Functions
    • The Concept of Combining Character Functions
    • Summary
    • Q&A
    • Workshop
  • Hour 12. Understanding Dates and Times
  • Part IV: Building Sophisticated Database Queries
  • Hour 13. Joining Tables in Queries
  • Hour 14. Using Subqueries to Define Unknown Data
  • Hour 15. Combining Multiple Queries into One
  • Part V: SQL Performance Tuning
  • Hour 16. Using Indexes to Improve Performance
  • Hour 17. Improving Database Performance
  • Part VI: Using SQL to Manage Users and Security
  • Hour 18. Managing Database Users
  • Hour 19. Managing Database Security
  • Part VII: Summarized Data Structures
  • Hour 20. Creating and Using Views and Synonyms
  • Hour 21. Working with the System Catalog
  • Part VIII: Applying SQL Fundamentals in Today's World
  • Hour 22. Advanced SQL Topics
  • Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
  • Hour 24. Extensions to Standard SQL
  • Part IX: Appendixes
  • Appendix A. Common SQL Commands
  • Appendix B. Using MySQL for Exercises
  • Appendix C. Answers to Quizzes and Exercises
  • Appendix D. CREATE TABLE Statements for Book Examples
  • Appendix E. INSERT Statements for Data in Book Examples
  • Appendix F. Glossary
  • Appendix G. Bonus Exercises

Miscellaneous Character Functions

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)

   

   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')

   
   

   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."

Share This