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

The Concept of Combining Character Functions

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.

Share This