Functions

SQL has a rich set of functions for manipulating the data. A function is a built in operation that manipulates or generates data. Functions normally have the format "FUNCTIONNAME(argument1, argument2, etc.)". In this template "functionname" is the name of the function such as COALESCE, UPPER, and SQRT. Function names are case insensitive so you can write them in lower or upper case. For ease of reading SQL they traditionally are in uppercase. But that tradition is not as strong as capitalizing keywords.

Functions have zero or more arguments that need to be provided in a comma separated list enclosed in parenthesis. The type of argument (string, number, etc.) is dependent on the function.

String Functions

String UPPER(), and LOWER() Functions

When we are working with conditional statements we had the problem of comparing strings. For example, in the person table the student names are in proper case. The first record (X00001) has a first name of "Alex". If we write a where clause WHERE person_first = "alex" or WHERE person_first = "ALEX" the code will never match any record. Instead we can use the UPPER() or LOWER() function to convert the record's text to uppercase or lowercase, respectively, for the purpose of evaluation.

SELECT
	p.person_id,
	p.person_first,
	p.person_last
FROM person AS p
WHERE UPPER(p.person_first) = 'ALEX';

person_id	person_first	person_last
X00001	Alex	Alexander

Please note the data that was returned has not been modified by the UPPER() function. The text "Alex" was only converted to uppercase for the purpose of evaluation. We can modify the output by using the UPPER() function inside the column selection.

SELECT
	p.person_id,
	UPPER(p.person_first),
	p.person_last
FROM person AS p
WHERE UPPER(p.person_first) = 'ALEX';

person_id	UPPER(p.person_first)	person_last
X00001	ALEX	Alexander

The database is a little confused and doesn't know what to call the person_first column so it created the column name "UPPER(p.person_first)". We can invent our own column name using the AS keyword.

SELECT
	p.person_id,
	UPPER(p.person_first) AS first_name,
	UPPER(p.person_last) AS last_name
FROM person AS p;

person_id	first_name	last_name
X00001	ALEX	ALEXANDER
X00002	BOB	BAKER
X00003	CHARLENE	CRYSTAL
X00004	DOLLY	DONALDSON
X00005	EDGAR	EAST
X00006	FREDDY	FREAKY
X00007	GRANT	GRADENOW
X00008	HELEN	HELENE
X00009	ISABELLA	INANOVA
X00010	JOHN	JACOBSON

String TRIM(), LTRIM(), and RTRIM() Functions

The TRIM() functions remove excess whitespace from strings. This can be very useful for formatting input from users. When the user enters a string, like a name, into any computer system they will sometimes add whitespace (space and tab characters) at the beginning or end of the input. If the program adding the data doesn't strip that whitespace it can cause problems with pattern matching and output from your work.

The argument for each function is the string to be trimmed. TRIM() removes whitespace from the left and right sides of the characters. LTRIM() will only remove whitespace from the left (or beginning) of the string. RTRIM() strips whitespace from the right side. None of these functions will remove whitespace from the middle of the string.

Let's try something a little different. Let's just select a string that is not part of any table.

SELECT ' Kate Katty ';

' Kate Katty '
Kate Katty

Yep, we can do that. Just SELECT a string. The database will return a table with only one cell containing the string, in this case ' Kate Katty ' with a space character at the beginning, end, and in between the two names. Now let's TRIM() the string.

SELECT TRIM(' Kate Katty ');

The result will be 'Kate Katty' without the leading and trailing whitespace. We can use TRIM() in our queries to have more accurate matches and to clean up the output.

SELECT
	p.person_id,
	UPPER(TRIM(p.person_first)) AS first_name,
	UPPER(TRIM(p.person_last)) AS last_name
FROM person AS p
WHERE UPPER(TRIM(p.person_first)) = 'JOHN';

String Concatenation

Concatenation is the formal name for splicing two character strings together. This can be done a few ways.

The CONCAT() function will concatenate two or more strings supplied as arguments. The query SELECT CONCAT('A', 'B', 'C'); will return "ABC".

SELECT
	p.person_id,
	CONCAT(p.person_first, ' ', p.person_last) AS name
FROM person AS p;

person_id	name
X00001	Alex Alexander
X00002	Bob Baker
X00003	Charlene Crystal
X00004	Dolly Donaldson
X00005	Edgar East
X00006	Freddy Freaky
X00007	Grant Gradenow
X00008	Helen Helene
X00009	Isabella Inanova
X00010	John Jacobson

In this example the CONCAT() function has spliced together the first and last names. Note that we also had to supply a string containing a space character or the first and last names would be run together.

An alternative to the CONCAT() function is to use plus signs between the strings. The code p.person_first + ' ' + p.person_last AS name will do the same as CONCAT().

Another alternative is the ANSI SQL standard concatenation symbol. Just replace the plus signs with two pipe symbols (||). The pipe symbol is above the enter key on your keyboard. It is the shift value for the backslash key. Example: p.person_first || ' ' || p.person_last AS name.

Casting

Beware when concatenating numbers. Concatenation is a function of strings not numbers. You can concatenate a number to a string if you convert it to a string first. This is known as casting.

The CONCAT() function will silently cast numbers to concatenate them. This is known as an implicit cast or implicit conversion.

But when you use plus signs or double pipe symbols you must do an explicit cast to a string. This is done a few ways. The CONVERT() function will do it. The first argument for CONVERT() is the data type to change the number into. For concatenation it should be CHAR or VARCHAR. The second argument is the number to be converted. For example, to convert the number 100 to a char use CONVERT(CHAR(3), 100).

The CAST() function is very similar but the arguments are reversed and separated with an AS keyword. This is a somewhat unusual way of doing function arguments. An example is CAST(100 AS char(3)).

Nesting Functions

Please note how we nested the UPPER() and TRIM() functions in the last example. When we nest functions they are resolved from the inside out. In the previous example the database applied TRIM() to the string. It then applied UPPER() to the trimmed string. In many cases (such as this one) the order doesn't matter. "UPPER(TRIM(p.person_first))" is the same as "TRIM(UPPER(p.person_first))". But in many cases, especially mathematical functions, the order is important. It can be very helpful to pick a few examples from the table and manually processes them step by step to make sure the results are what you want.