SQL Functions: Working With Strings

String functions expand the possibilities of selecting and working with data in SQL, particularly with…well, strings.

They’re particularly useful when you are met with specific requirements for returning and presenting data.

Here is a breakdown of the most common functions:

CONCAT

CONCAT() allows you to concatenate strings i.e. glue more than one string together into one larger string:

SELECT
    CONCAT(firstname, ‘ ‘, lastname)
FROM customers;

Returns:
John Smith

Similarly, CONCAT_WS() (i.e. concatenate with separator) adds multiple expressions together with a separator. It takes the separator as its first argument:

SELECT
    CONCAT_WS(‘ ‘, firstname, lastname)
FROM customers;

Returns:
John Smith

SUBSTRING

SUBSTRING() performs the same way it does in most other languages. It allows you to split a string into smaller ’substrings’:

SELECT SUBSTRING(‘Hello world’, 1, 5);

Returns:
Hello

Note that in SQL, strings are counted from 1, not from 0, as is the case in most programming languages where counting is zero-based.

You can use the abbreviated version SUBSTR() to do the same thing and achieve the same result.

REPLACE

This function will replace all occurrences of a substring (within an existing string) with a new substring. That sounds more complicated than it is:

SELECT REPLACE(‘Hello world’, ‘llo’, ‘y’);

Returns:
Hey world

REVERSE

You probably won’t find yourself having to use this function very commonly, but it could come in handy on those rare occasions.

It does what it says on the tin i.e. reverses a string and returns the result:

SELECT REVERSE(‘Hello world’);

Returns:
dlrow olleH

CHAR_LENGTH

CHAR_LENGTH() will return the length of a string. That’s it.

SELECT firstname, CHAR_LENGTH(firstname) AS firstname_length FROM customers;

Returns:
firstname   firstname_length
John        4

UPPER

UPPER() converts a string to its uppercase equivalent:

SELECT UPPER(‘Hello world’);

Returns:
HELLO WORLD

LOWER

Same deal as the latter, but the opposite:

SELECT LOWER(‘Hello world’);

Returns:
hello world

Conclusion

Although seemingly simple, string functions can be combined into any number of creative combinations, aiding complex queries to present data exactly as intended.