Functions

Part:   1   2 

This chapter describes:

• Flow control functions.
• Character string functions.
• Numeric functions.
• Date and time functions.
• Cast Function.

All notes in chapter are based on SQL-92 and MySQL server.

Flow Control Functions

"CASE value WHEN val_1 THEN res_1 WHEN val_2 THEN res_2 ... ELSE res_0 END" - Returns res_1 if value matches val_1, or res_2 if value matches val_2, ..., or res_0 if value matches none of the given values.

"CASE WHEN con_1 THEN res_1 WHEN con_2 THEN res_2 ... ELSE res_0 END" - Returns res_1 if con_1 is true, or res_2 if con_2 is true, ..., or res_0 if none of the given conditions is true.

"IF(condition, res_1, res_2)" - Returns res_1 if condition is true, otherwise returns res_2.

"IFNULL(res_1, res_2)" - Returns res_1 if res_1 is not null, otherwise returns res_2.

Character String Functions

"CONCAT(str_1, str_2)" - Returns a character string made by joining the specified two strings. This is a replacement of the concatenation operator, "||", defined in SQL-92.

"CHAR_LENGTH(string)" - Returns a number indicating the number of characters of the specified string.

"BIT_LENGTH(string)" - Returns a number indicating the number of bits of the specified string.

"SUBSTRING(string, FROM position FOR length)" - Returns a character string made by extracting a portion of the specified string according to the specified position and the specified length.

"UPPER(string)" - Returns a character string made by converting the specified string to upper case characters.

"LOWER(string)" - Returns a character string made by converting the specified string to lower case characters.

"TRIM(LEADING|TRAILING|BOTH char FROM string)" - Returns a character string made by removing leading (trailing or both) padding characters from the specified string.

"POSITION(substr IN str)" - Returns a number indicating the position of a short string inside a long string.

Examples of character strings functions, CharStringFunctions.sql:

-- CharStringFunctions.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
SELECT CONCAT('abc','xyz'); -- MySQL does not support ||
SELECT UPPER('fortran');
SELECT SUBSTRING('Hello world!',7,5); -- Starting position is 1
SELECT TRIM('   011.99000 '); -- Triming ' ' on both ends
SELECT TRIM(BOTH ' ' FROM '   011.99000 ');
SELECT TRIM(LEADING '0' FROM '011.99000');
SELECT TRIM(TRAILING '0' FROM '011.99000');
SELECT CHAR_LENGTH('Hello world!');
SELECT POSITION('world' IN 'Hello world!');

Output:

CONCAT('abc','xyz')
abcxyz
UPPER('fortran')
FORTRAN
SUBSTRING('Hello world!',7,5)
world
TRIM('   011.99000 ')
011.99000
TRIM(BOTH ' ' FROM '   011.99000 ')
011.99000
TRIM(LEADING '0' FROM '011.99000')
11.99000
TRIM(TRAILING '0' FROM '011.99000')
011.99
CHAR_LENGTH('Hello world!')
12
POSITION('world' IN 'Hello world!')
7

(Continued on next part...)

Part:   1   2