Advanced Query Writing

Before we move on to Data Manipulation Language in Chapter 7, this chapter is intended to round out your knowledge of SQL queries by covering some advanced topics that were only brushed upon in Chapters 4 and 5. It covers the following topics:

• Advanced SQL functions, including character, mathematical, and date/time functions
  
• A description of how to take advantage of views
  
• A description of how to use SQL to generate SQL statements
  
• Information on the SQL CASE expression and its use in forming statements that have portions that are only executed under predefined conditions

Advanced SQL Functions

SQL functions were introduced in Chapter 4. The topics that follow describe functions that were not covered in Chapter 4 but which you will find useful. In addition to character and mathematical functions, selected date and time functions are also included. Remember that all SQL functions have a common characteristic in that they return a single value, so they are useful in several places in SQL statements, including the SELECT statement column list and WHERE clause. As a reminder, there are many more implementation-specific functions provided by the various DBMS vendors, so always check the vendor-supplied documentation for more useful functions.

Character Functions

Character functions operate on character data. This topic presents some commonly used functions in addition to those covered in Chapter 4. Except as noted, you will find the function supported by the most popular SQL implementations, including Microsoft SQL Server, Oracle, DB2, and MySQL.

REPLACE

The REPLACE function searches a character string and replaces characters found in a search string with characters listed in a replacement string. Here is the general syntax:

REPLACE(character_string, search_string, replacement_string)

• character_string is the string to be searched and is most often a table column name, but it can be any expression that yields a character string.
  
• search_string is the string of one or more characters to be found in character_string.
  
•  replacement_string is the string that replaces any occurrences of search_string that are found in character_string.

Here is an example that replaces all hyphens (dashes) found in a person’s phone number with periods (only the first two rows in the result set are shown):

SELECT PERSON_PHONE,
       REPLACE(PERSON_PHONE,'-','.') AS DISPLAY_PHONE
  FROM PERSON;
PERSON_PHONE     DISPLAY_PHONE
---------------  ---------------
230-229-8976     230.229.8976
401-617-7297     401.617.7297

LTRIM

The LTRIM function removes any leading (left-hand) spaces in a character string. Note that only leading spaces are removed—embedded and trailing spaces are left in the string. There is no data with leading and/or trailing spaces in the video store database, so here is a general example:

LTRIM (' String with spaces ')
Returns this string: 'String with spaces '

RTRIM

The RTRIM function works like LTRIM, but it removes trailing spaces. If you need to remove both leading and trailing spaces, you can nest LTRIM and RTRIM like this:

RTRIM(LTRIM (' String with spaces '))
Returns this string: 'String with spaces'

NOTE: Oracle provides a convenient function named TRIM that trims both leading and trailing spaces. For other implementations, you can always nest the LTRIM and RTRIM functions and achieve the same result.