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() 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() 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.
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
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() 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() converts a string to its uppercase equivalent:
SELECT UPPER(‘Hello world’); Returns: HELLO WORLD
Same deal as the latter, but the opposite:
SELECT LOWER(‘Hello world’); Returns: hello world
Although seemingly simple, string functions can be combined into any number of creative combinations, aiding complex queries to present data exactly as intended.