Which string functions should I actually know for a SQL interview?
There are about eight that cover almost everything you will ever need, and the names differ slightly from engine to engine.
UPPER(text) UPPERCASE(text) the text in capitals
LOWER(text) LOWER(text) the text in small letters
TRIM(text) LTRIM/RTRIM the text without spaces at the ends
LENGTH(text) LEN(text) how many characters there are
SUBSTRING(a,b) SUBSTR(a,b) a piece of the text, from position b
CONCAT(a,b) a || b two pieces joined together
REPLACE(a,b,c) STUFF(a,b,c,d) every b replaced by c
POSITION(x,a) STRPOS(a,x) where x appears in a
So which of them should you really know? The ones you will use every single day are the first four. The function UPPER and the function LOWER are for comparing text without caring about capital letters. The function TRIM is for cleaning up text that a person typed. And the function LENGTH is for checking the length of something, or for finding the last character of a string.
Then there are two that are worth knowing well. The function SUBSTRING takes a piece out of the text, and it has two forms. With one position it takes everything from that point to the end. With two positions it takes the piece between them, and the first position is included while the second is not.
And the function CONCAT joins pieces of text together. The portable way to do it in the standard is the two vertical bars, and the function CONCAT works in MySQL and SQLite and PostgreSQL. In SQL Server you use the plus sign.
There is one more that is worth knowing about, and it is the one that catches people out. The function COALESCE is not a string function, but it is the function you use with strings all the time, and it is the way to give a missing value a sensible fallback.
One warning about all of them. They are slow on large tables, because the engine has to work out the answer for every row. So a function wrapped around a column in the WHERE clause stops an index from being used. If you have to search a lot of text, store it in a way that is already tidy.
The next two questions in this topic put the functions to work.