Commonly used string functions in MySQL

Note that many of these functions can be executed in some combination.

  • Concatenation with CONCAT
SELECT CONCAT('Hi', ' ', 'there');

One can add any reasonable number of arguements to form a concatenated string. Entire columns can be combined e.g.

SELECT CONCAT(column1, " ", column2);

Note that CONCAT output is treated as a column. One can utilise two columns, concat() the columns and print them under custom column heading (table is called register):

SELECT firstName AS 'First', lastName AS 'Last', CONCAT(firstName, ' ', lastName) AS 'full name' FROM register;

If a consistently used separator is used (“ “ or ‘-‘ etc.) then one can use CONCAT_WS(separator, all_other_substrings).

  • Extracting part of a string using SUBSTRING
SELECT SUBSTRING(string, x, y) FROM tableName;

This function is one-based, not zero-based. It extracts the x-th to y-th characters (inclusive) of string.

SELECT SUBSTRING('Hey Dude!', 5, 8);

The above statement returns a table with the string ‘Dude’. A statement:

SELECT SUBSTRING('Hey Dude!', -5);

…would print out the first 5 first characters starting from the end of the string. Recall, that double quotes can be used if the string contains single quotes. Likewise single quotes should be used when double quotes are part of the string.

SELECT SUBSTRING("I'm full of apostrophes''''", -4);

The above statement would print “’’’’”.

As a side, SUBSTR() is identical to SUBSTRING().

  • Replacing strings or parts of strings with REPLACE
SELECT REPLACE('I am a useless statement', 'useless', 'helpful');

The above statement looks for any instance of the second argument and replaces any instance with the third argument. The arguments are case sensitive.

REPLACE can in many ways be used to extend strings by replacing whitespace with meaningful words.

SELECT REPLACE(' man_and bananas', ' ', ' the ');
  • Reversing strings with REVERSE
SELECT REVERSE('1234567890');

The above statement yields the string ‘0987654321’.

  • Deducing the length of a string with CHAR_LENGTH

Return an integer of the number of CHAR’s of a string.

SELECT CHAR_LENGTH("greetings");

The above statement returns 9.

  • Setting upper and lower case with UPPER and LOWER

Both functions perform as implied, changing the case of all char’s of a string.

SELECT UPPER('try ME);
SELECT LOWER("NO try ME");

The above statements result in ‘TRY ME’ and “no try me”.