Hour 11. Restructuring the Appearance of Data
What You’ll Learn in This Hour:
• Introduction to character functions
• How and when to use character functions
• Examples of ANSI SQL functions
• Examples of common implementation-specific functions
• Overview of conversion functions
• How and when to use conversion functions
In this hour, you learn how to restructure the appearance of output results using some American National Standards Institute (ANSI) standard functions, other functions based on the standard, and several variations used by some major SQL implementations.
By the Way: The ANSI Standard Is Not Rigid
The ANSI concepts discussed in this book are just that—concepts. Standards provided by ANSI are simply guidelines for how the use of SQL in a relational database should be implemented. With that thought, keep in mind that the specific functions discussed in this hour are not necessarily the exact functions that you might use in your particular implementation. Yes, the concepts are the same, and the way the functions work are generally the same, but function names and actual syntax might differ.
ANSI Character Functions
Character functions are functions that represent strings in SQL in formats different from the way they are stored in the table. The first part of this hour discusses the concepts for character functions as covered by ANSI. The second part of this hour shows real-world examples using functions that are specific to various SQL implementations. The most common forms of ANSI character functions deal with operations for concatenation, substrings, and TRANSLATE.
Concatenation is the process of combining two strings into one. For example, you might want to concatenate an individual’s first and last names into a single string for the complete name.
JOHN concatenated with SMITH produces JOHN SMITH.
The concept of substring is the capability to extract part of a string, or a “sub” of the string. For example, the following values are substrings of JOHNSON:
• J
• JOHN
• JO
• ON
• SON
The TRANSLATE function translates a string, character by character, into another string. There are normally three arguments with the TRANSLATE function: the string to be converted, a list of the characters to convert, and a list of the substitution characters. Implementation examples are shown in the next part of this hour.
Common Character Functions
You use character functions mainly to compare, join, search, and extract a segment of a string or a value in a column. Several character functions are available to the SQL programmer.
The following sections illustrate the application of ANSI concepts in some of the leading implementations of SQL, such as Microsoft SQL Server, MySQL, and Oracle.
The CONCAT Function
The CONCAT function, along with most other functions, is represented slightly differently among various implementations. The following examples show the use of concatenation in Oracle and SQL Server.
Let’s say you want to concatenate JOHN and SON to produce JOHNSON. In Oracle, your code looks like this:
SELECT 'JOHN' | | 'SON'
In SQL Server, your code appears as follows:
SELECT 'JOHN' + 'SON'
In MySQL, your code looks like this:
SELECT CONCAT('JOHN' , 'SON')
Now for an overview of the syntaxes. The syntax for Oracle is
COLUMN_NAME | | [ '' | | ] COLUMN_NAME [ COLUMN_NAME ]
The syntax for SQL Server is
COLUMN_NAME + [ '' + ] COLUMN_NAME [ COLUMN_NAME ]
The syntax for MySQL is
CONCAT(COLUMN_NAME , [ '' , ] COLUMN_NAME [ COLUMN_NAME ])
Both MySQL as well as Oracle employ the CONCAT function. You can use it to get the concatenation of pairs of strings just like the shortened syntax of + for SQL Server and the double pipe (| |) for Oracle. The main difference between the two versions is that the Oracle version is limited to two values to be concatenated, whereas you can use the MySQL version for large numbers of values. In addition, remember that, because this operation is for string values, any numeric values must be converted to strings before concatenation. Unfortunately, Microsoft SQL Server does not support the CONCAT function. Some examples of utilizing concatenation in its various formats are shown next.
This SQL Server statement concatenates the values for city and state into one value:
SELECT CITY + STATE FROM EMPLOYEE_TBL;
This Oracle statement concatenates the values for city and state into one value, placing a comma between the values for city and state:
SELECT CITY | |', '| | STATE FROM EMPLOYEE_TBL;
Alternatively for Oracle, if you wanted to use the CONCAT statement to achieve the preceding result, you would be unable to do so because you are concatenating more than two values.
By the Way: Use of Quotation Marks for Special Characters
Notice the use of single quotation marks and a comma in the preceding SQL statement. Most characters and symbols are allowed if they’re enclosed by single quotations marks. Some implementations might use double quotation marks for literal string values.
This SQL Server statement concatenates the values for city and state into one value, placing a space between the two original values:
SELECT CITY + '' + STATE FROM EMPLOYEE_TBL;
This SQL Server statement concatenates the last name with the first name and inserts a comma between the two original values:
SELECT LAST_NAME + ', ' + FIRST_NAME NAME
FROM EMPLOYEE_TBL;
NAME
---------------
STEPHENS, TINA
PLEW, LINDA
GLASS, BRANDON
GLASS, JACOB
WALLACE, MARIAH
SPURGEON, TIFFANY
6 rows selected.
The TRANSLATE Function
The TRANSLATE function searches a string of characters and checks for a specific character, makes note of the position found, searches the replacement string at the same position, and then replaces that character with the new value. The syntax is
TRANSLATE(CHARACTER SET, VALUE1, VALUE2)
The next SQL statement substitutes every occurrence of I in the string with A, every occurrence of N with B, and all occurrences of D with C:
SELECT TRANSLATE (CITY,'IND','ABC' FROM EMPLOYEE_TBL) CITY_TRANSLATION
The following example illustrates the use of TRANSLATE with real data:
SELECT CITY, TRANSLATE(CITY,'IND','ABC')
FROM EMPLOYEE_TBL;
CITY CITY_TRANSLATION
---------- -----------------
GREENWOOD GREEBWOOC
INDIANAPOLIS ABCAABAPOLAS
WHITELAND WHATELABC
INDIANAPOLIS ABCAABAPOLAS
INDIANAPOLIS ABCAABAPOLAS
INDIANAPOLIS ABCAABAPOLAS
6 rows selected.
Notice in this example that all occurrences of I were replaced with A, N with B, and D with C. In the city INDIANAPOLIS, IND was replaced with ABC, but in GREENWOOD, D was replaced with C. Also notice how the value WHITELAND was translated.
Both MySQL and Oracle support the use of the TRANSLATE function. Microsoft SQL Server does not currently support the use of TRANSLATE.
The REPLACE Function
The REPLACE function replaces every occurrence of a character or string with another specified character or string. The use of this function is similar to the TRANSLATE function except only one specific character or string is replaced within another string. The syntax is
REPLACE('VALUE', 'VALUE', [ NULL ] 'VALUE')
This statement returns all the first names and changes any occurrence of T to B:
SELECT REPLACE(FIRST_NAME,'T', 'B') FROM EMPLOYEE_TBL
This statement returns all the cities in EMPLOYEE_TBL and returns the same cities with each I replaced with a Z:
SELECT CITY, REPLACE(CITY,'I','Z')
FROM EMPLOYEE_TBL;
CITY REPLACE(CITY)
------------ -------------
GREENWOOD GREENWOOD
INDIANAPOLIS ZNDZANAPOLZS
WHITELAND WHZTELAND
INDIANAPOLIS ZNDZANAPOLZS
INDIANAPOLIS ZNDZANAPOLZS
INDIANAPOLIS ZNDZANAPOLZS
6 rows selected.
Microsoft SQL Server, MySQL, and Oracle all support the ANSI version of the syntax.
The UPPER Function
Most implementations have a way to control the case of data by using functions. The UPPER function converts lowercase letters to uppercase letters for a specific string.
The syntax is as follows:
UPPER(character string)
This SQL statement converts all characters in the column to uppercase:
SELECT UPPER(CITY)
FROM EMPLOYEE_TBL;
UPPER(CITY)
-------------
GREENWOOD
INDIANAPOLIS
WHITELAND
INDIANAPOLIS
INDIANAPOLIS
INDIANAPOLIS
6 rows selected.
Microsoft SQL Server, MySQL, and Oracle all support this syntax. Additionally, MySQL supports a synonym for the UPPER function by using UCASE. Because both functions accomplish the same task, you are better served to follow the ANSI syntax.
The LOWER Function
The converse of the UPPER function, the LOWER function, converts uppercase letters to lowercase letters for a specific string.
The syntax is as follows:
LOWER(character string)
This SQL statement converts all characters in the column to lowercase:
SELECT LOWER(CITY)
FROM EMPLOYEE_TBL;
LOWER(CITY)
-------------
greenwood
indianapolis
whiteland
indianapolis
indianapolis
indianapolis
6 rows selected.
The LOWER function is supported in Microsoft SQL Server, Oracle, and MySQL. Like the UPPER function, MySQL supports a synonym LCASE, but as discussed with the UPPER function, it is often better to follow the ANSI standard.
The SUBSTR Function
Taking an expression’s substring is common in most implementations of SQL, but the function name might differ, as shown in the following Oracle and SQL Server examples.
The syntax for Oracle is
SUBSTR(COLUMN NAME, STARTING POSITION, LENGTH)
The syntax for SQL Server is
SUBSTRING(COLUMN NAME, STARTING POSITION, LENGTH)
The only difference between the two implementations is the spelling of the function name.
This SQL statement returns the first three characters of EMP_ID:
SELECT SUBSTRING(EMP_ID,1,3) FROM EMPLOYEE_TBL
This SQL statement returns the fourth and fifth characters of EMP_ID:
SELECT SUBSTRING(EMP_ID,4,2) FROM EMPLOYEE_TBL
This SQL statement returns the sixth through the ninth characters of EMP_ID:
SELECT SUBSTRING(EMP_ID,6,4) FROM EMPLOYEE_TBL
The following is an example that is compatible with Microsoft SQL Server and MySQL:
SELECT EMP_ID, SUBSTRING(EMP_ID,1,3)
FROM EMPLOYEE_TBL;
EMP_ID
-------------
311549902 311
442346889 442
213764555 213
313782439 313
220984332 220
443679012 443
6 rows affected.
The following SQL statement is what you use for Oracle:
SELECT EMP_ID, SUBSTR(EMP_ID,1,3)
FROM EMPLOYEE_TBL;
EMP_ID
-------------
311549902 311
442346889 442
213764555 213
313782439 313
220984332 220
443679012 443
6 rows selected.
By the Way: Output Statements Differ Between Implementations
Notice the difference in the feedback of the two queries. The first example returns the feedback 6 rows affected, and the second returns 6 rows selected. You will see differences such as this between the various implementations.
The INSTR Function
The INSTR function searches a string of characters for a specific set of characters and reports the position of those characters. The syntax is as follows:
INSTR(COLUMN NAME, 'SET',
[ START POSITION [ , OCCURRENCE ] ]);
This SQL statement returns the position of the first occurrence of the letter I for each state in EMPLOYEE_TBL:
SELECT INSTR(STATE,'I',1,1) FROM EMPLOYEE_TBL;
This SQL statement looks for the first occurrence of the letter A in the PROD_DESC column:
SELECT PROD_DESC,
INSTR(PROD_DESC,'A',1,1)
FROM PRODUCTS_TBL;
PROD_DESC INSTR(PROD_DESC,'A',1,1)
----------------------- ------------------------
WITCH COSTUME 0
PLASTIC PUMPKIN 18 INCH 3
FALSE PARAFFIN TEETH 2
LIGHTED LANTERNS 10
ASSORTED COSTUMES 1
CANDY CORN 2
PUMPKIN CANDY 10
PLASTIC SPIDERS 3
ASSORTED MASKS 1
KEY CHAIN 7
OAK BOOKSHELF 2
11 rows selected.
Notice that if the searched character A is not found in a string, the value 0 is returned for the position.
The INSTR function is specific to the MySQL and Oracle implementations, although you can use a similar function, CHARINDEX, for Microsoft SQL Server implementations.
The LTRIM Function
The LTRIM function is another way of clipping part of a string. This function and SUBSTRING are in the same family. LTRIM trims characters from the left of a string. The syntax is
LTRIM(CHARACTER STRING [ ,'set' ])
This SQL statement trims the characters LES from the left of all names that are LESLIE:
SELECT LTRIM(FIRST_NAME,'LES') FROM CUSTOMER_TBL WHERE FIRST_NAME ='LESLIE';
This SQL statement returns the position of the employee with the word SALES trimmed from the left side of the character string:
SELECT POSITION, LTRIM(POSITION,'SALES')
FROM EMPLOYEE_PAY_TBL;
POSITION LTRIM(POSITION,
------------- ----------------
MARKETING MARKETING
TEAM LEADER TEAM LEADER
SALES MANAGER MANAGER
SALESMAN MAN
SHIPPER HIPPER
SHIPPER HIPPER
6 rows selected.
The S in SHIPPER was trimmed off, even though SHIPPER does not contain the string SALES. The first four characters of SALES were ignored. The searched characters must appear in the same order of the search string and must be on the far left of the string. In other words, LTRIM trims off all characters to the left of the last occurrence in the search string.
The LTRIM function is supported in Microsoft SQL Server, MySQL, and Oracle.
The RTRIM Function
Like LTRIM, the RTRIM function trims characters, but this time from the right of a string. The syntax is
RTRIM(CHARACTER STRING [ ,'set' ])
This SQL statement returns the first name BRANDON and trims the ON, leaving BRAND as a result:
SELECT RTRIM(FIRST_NAME, 'ON') FROM EMPLOYEE_TBL WHERE FIRST_NAME = 'BRANDON';
This SQL statement returns a list of the positions in PAY_TBL as well as the positions with the letters ER trimmed from the right of the character string:
SELECT POSITION, RTRIM(POSITION,'ER')
FROM EMPLOYEE_PAY_TBL;
POSITION RTRIM(POSITION,
------------- ---------------
MARKETING MARKETING
TEAM LEADER TEAM LEAD
SALES MANAGER SALES MANAG
SALESMAN SALESMAN
SHIPPER SHIPP
SHIPPER SHIPP
6 rows selected.
The string ER was trimmed from the right of all applicable strings.
The RTRIM function is supported in Microsoft SQL Server, MySQL, and Oracle.
The DECODE Function
The DECODE function is not ANSI—at least not at the time of this writing—but its use is shown here because of its great power. This function is used mainly in Oracle and PostgreSQL implementations. DECODE searches a string for a value or string; if the string is found, an alternative string is displayed as part of the query results.
DECODE(COLUMN NAME, 'SEARCH1', 'RETURN1',[ 'SEARCH2', 'RETURN2', 'DEFAULT VALUE'])
This query searches the value of all last names in EMPLOYEE_TBL; if the value SMITH is found, JONES is displayed in its place. Any other names are displayed as OTHER, which is called the default value:
SELECT DECODE(LAST_NAME,'SMITH','JONES','OTHER') FROM EMPLOYEE_TBL;
In the following example, DECODE is used on the values for CITY in EMPLOYEE_TBL:
SELECT CITY,
DECODE(CITY,'INDIANAPOLIS','INDY',
'GREENWOOD','GREEN','OTHER')
FROM EMPLOYEE_TBL;
CITY DECOD
------------ ------
GREENWOOD GREEN
INDIANAPOLIS INDY
WHITELAND OTHER
INDIANAPOLIS INDY
INDIANAPOLIS INDY
INDIANAPOLIS INDY
6 rows selected.
The output shows the value INDIANAPOLIS displayed as INDY, GREENWOOD displayed as GREEN, and all other cities displayed as OTHER.
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.
The LENGTH Function
The LENGTH function is a common one that finds the length of a string, number, date, or expression in bytes. The syntax is
LENGTH(CHARACTER STRING)
This SQL statement returns the product description and its corresponding length:
SELECT PROD_DESC, LENGTH(PROD_DESC)
FROM PRODUCTS_TBL;
PROD_DESC LENGTH(PROD_DESC)
------------------------ -----------------
WITCH 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.
The LENGTH function is supported in both MySQL and Oracle. Microsoft SQL Server uses a shortened version LEN instead, but the functionality is the same.
The IFNULL Function (NULL Value Checker)
The IFNULL function returns data from one expression if another expression is NULL. You can use IFNULL with most data types; however, the value and the substitute must be the same data type. The syntax is
IFNULL('VALUE', 'SUBSTITUTION')
This SQL statement finds NULL values and substitutes 9999999999 for them:
SELECT PAGER, IFNULL(PAGER,9999999999)
FROM EMPLOYEE_TBL;
PAGER IFNULL(PAGER,
---------- -------------
9999999999
9999999999
3175709980 3175709980
8887345678 8887345678
9999999999
9999999999
6 rows selected.
Only NULL values were represented as 9999999999.
IFNULL is supported only in the MySQL implementation. However, Microsoft SQL Server uses a similar function, ISNULL, that achieves the same result. Oracle utilizes the COALESCE function.
The COALESCE Function
The COALESCE function is similar to the IFNULL function in that it specifically replaces NULL values within the result set. The COALESCE function, however, can accept a whole set of values and checks each one in order until it finds a non-NULL result. If a non-NULL result is not present, COALESCE returns a NULL value.
The following example demonstrates the COALESCE function by giving us the first non-NULL value of BONUS, SALARY, and PAY_RATE:
SELECT EMP_ID, COALESCE(BONUS,SALARY,PAY_RATE)
FROM EMPLOYEE_PAY_TBL;
EMP_ID COALESCE(BONUS,SALARY,PAY_RATE)
---------- --------------------------------------------------------
213764555 2000.00
220984332 11.00
311549902 40000.00
313782439 1000.00
442346889 14.75
443679012 15.00
6 rows selected.
The COALESCE function is supported in Microsoft SQL Server, MySQL, and Oracle.
The LPAD Function
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
------------------------------
.................WITCH 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.
The LPAD function is supported in both MySQL and Oracle. Unfortunately, no alternative is available for Microsoft SQL Server.
The RPAD Function
The RPAD (right pad) function adds 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
------------------------------
WITCH 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.
The RPAD function is available in both MySQL and Oracle. Unfortunately, no substitute is available for Microsoft SQL Server.
The ASCII Function
The ASCII function returns the ASCII representation of the leftmost character of a string. The syntax is
ASCII(CHARACTER SET)
The following are some examples:
• ASCII('A') returns 65
• ASCII('C') returns 67
• ASCII('a') returns 97
For more information, you may refer to the ASCII chart located at www.asciitable.com.
The ASCII function is supported in Microsoft SQL Server, MySQL, and Oracle.
Mathematical Functions
Mathematical functions are standard across implementations. Mathematical functions enable you to manipulate numeric values in a database according to mathematical rules.
The most common functions include the following:
• Absolute value (ABS)
• Rounding (ROUND)
• Square root (SQRT)
• Sign values (SIGN)
• Power (POWER)
• Ceiling and floor values (CEIL(ING), FLOOR)
• Exponential values (EXP)
• SIN, COS, TAN
The general syntax of most mathematical functions is
FUNCTION(EXPRESSION)
All the mathematical functions are supported in Microsoft SQL Server, MySQL, and Oracle.
Conversion Functions
Conversion functions convert a data type into another data type. For example, perhaps you have data that is normally stored in character format, but occasionally you want to convert the character format to numeric to make calculations. Mathematical functions and computations are not allowed on data that is represented in character format.
The following are general types of data conversions:
• Character to numeric
• Numeric to character
• Character to date
• Date to character
The first two types of conversions are discussed in this hour. The remaining conversion types are discussed in Hour 12, “Understanding Dates and Times.”
Converting Character Strings to Numbers
You should notice two things regarding the differences between numeric data types and character string data types:
By the Way: Converting to Numeric Values
For a character string to be converted to a number, the characters must typically be 0 through 9. The addition symbol (+), minus symbol (–), and period (.) can also be used to represent positive numbers, negative numbers, and decimals. For example, the string STEVE cannot be converted to a number, whereas an individual’s Social Security number can be stored as a character string but can easily be converted to a numeric value via use of a conversion function.
• You can use arithmetic expressions and functions on numeric values.
• Numeric values are right-justified in the output results, whereas character string data types are left-justified.
When a character string is converted to a numeric value, the value takes on the two attributes just mentioned.
Some implementations might not have functions to convert character strings to numbers, whereas others have such conversion functions. In either case, consult your implementation documentation for specific syntax and rules for conversions.
By the Way: Some Systems Do the Conversions for You
Some implementations might implicitly convert data types when necessary. This means that the system makes the conversion for you when changing between data types. In these cases, the use of conversion functions is unnecessary. Check your implementation’s documentation to see which types of implicit conversions are supported.
The following is an example of a numeric conversion using an Oracle conversion function:
SELECT EMP_ID, TO_NUMBER(EMP_ID)
FROM EMPLOYEE_TBL;
EMP_ID TO_NUMBER(EMP_ID)
--------- -----------------
311549902 311549902
442346889 442346889
213764555 213764555
313782439 313782439
220984332 220984332
443679012 443679012
6 rows selected.
The employee identification is right-justified following the conversion.
Converting Numbers to Character Strings
Converting numeric values to character strings is precisely the opposite of converting characters to numbers.
The following is an example of converting a numeric value to a character string using a Transact-SQL conversion function for Microsoft SQL Server:
SELECT PAY = PAY_RATE, NEW_PAY = STR(PAY_RATE)
FROM EMPLOYEE_PAY_TBL
WHERE PAY_RATE IS NOT NULL;
PAY NEW_PAY
---------- -------
17.5 17.5
14.75 14.75
18.25 18.25
12.8 12.8
11 11
15 15
6 rows affected.
Did You Know?: Different Data Is Output in Different Ways
The data’s justification is the simplest way to identify a column’s data type.
The following is the same example using an Oracle conversion function:
SELECT PAY_RATE, TO_CHAR(PAY_RATE)
FROM EMPLOYEE_PAY_TBL
WHERE PAY_RATE IS NOT NULL;
PAY_RATE TO_CHAR(PAY_RATE)
---------- -----------------
17.5 17.5
14.75 14.75
18.25 18.25
12.8 12.8
11 11
15 15
6 rows selected.
Combining Character Functions
You can combine most functions in an SQL statement. SQL would be far too limited if function combinations were not allowed. 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. This example uses the CONCAT function to combine the strings for output:
SELECT CONCAT(LAST_NAME,', ',FIRST_NAME) NAME,
CONCAT(SUBSTR(EMP_ID,1,3),'-',
SUBSTR(EMP_ID,4,2),'-',
SUBSTR(EMP_ID,6,4)) AS 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.
This example uses the LENGTH function and the addition 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:
SELECT SUM(LENGTH(LAST_NAME) + LENGTH(FIRST_NAME)) TOTAL
FROM EMPLOYEE_TBL;
TOTAL
-----------
71
1 row selected.
By the Way: How Embedded Functions Are Resolved
When embedding functions within functions in an SQL statement, remember that the innermost function is resolved first, and then each function is subsequently resolved from the inside out.
Summary
You have been introduced to various functions used in an SQL statement—usually a query—to modify or enhance the way output is represented. Those functions include character, mathematical, and conversion functions. It is important to realize that the ANSI standard is a guideline for how SQL should be implemented by vendors, but it does not dictate the exact syntax or necessarily place limits on vendors’ innovations. Most vendors have standard functions and conform to the ANSI concepts, but each vendor has its own specific list of available functions. The function name might differ and the syntax might differ, but the concepts with all functions are the same.
Q&A
Q. Are all functions in the ANSI standard?
A. No, not all functions are exactly ANSI SQL. Functions, like data types, are often implementation dependent. Most implementations contain supersets of the ANSI functions; many have a wide range of functions with extended capability, whereas other implementations seem to be somewhat limited. Several examples of functions from selected implementations are included in this hour. However, because so many implementations use similar functions (although they might slightly differ), check your particular implementation for available functions and their usage.
Q. Is the data actually changed in the database when using functions?
A. No. Data is not changed in the database when using functions. Functions are typically used in queries to manipulate the output’s appearance.
Workshop
The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
Quiz
1. Match the descriptions with the possible functions.
2. True or false: Using functions in a SELECT statement to restructure the appearance of data in output also affects the way the data is stored in the database.
3. True or false: The outermost function is always resolved first when functions are embedded within other functions in a query.
Exercises
1. Type the following code at the mysql> prompt to concatenate each employee’s last name and first name:
SELECT CONCAT(LAST_NAME, ', ', FIRST_NAME)
FROM EMPLOYEE_TBL;
How would the same statement be applied in Oracle and SQL Server?
2. Type the following MySQL code to print each employee’s concatenated name and his or her area code:
SELECT CONCAT(LAST_NAME, ', ', FIRST_NAME), SUBSTRING(PHONE, 1, 3)
FROM EMPLOYEE_TBL;
Try writing the same code in SQL Server and Oracle.
3. Write an SQL statement that lists employee email addresses. Email is not a stored column. The email address for each employee should be as follows:
For example, John Smith’s email address is [email protected].
4. Write an SQL statement that lists each employee’s name, employee ID, and phone number in the following formats:
a. The name should be displayed as SMITH, JOHN.
b. The employee ID should be displayed as 999-99-9999.
c. The phone number should be displayed as (999)999-9999.