String functions: MySQL | Himanshu Sharma

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonUse MySQL's string functions to combine, extract, search and clean text values in queries.

Functions in a query

A function takes values in parentheses and returns a value. You can use them in the select list, in WHERE, in ORDER BY and in UPDATE ... SET. MySQL has hundreds; the string functions below cover most everyday text work. They are the SQL equivalent of the text helpers in a programming language.

Joining text

CONCAT joins its arguments:

sql

SELECT CONCAT(first_name, ' ', last_name) AS full_name, CONCAT(city, ', ', country) AS place FROM customers LIMIT 3;

result

+--------------+---------------+
| full_name    | place         |
+--------------+---------------+
| Asha Verma   | Delhi, India  |
| Ben Carter   | London, UK    |
| Chloe Dubois | Paris, France |
+--------------+---------------+

If any argument is NULL the whole result is NULL. CONCAT_WS ("with separator") puts a separator between the parts and skips NULLs, which is usually what you want for optional pieces such as an address:

sql

SELECT CONCAT('Tel: ', phone) AS with_concat, CONCAT_WS(' | ', first_name, phone, city) AS with_ws FROM customers WHERE id IN (1, 2);

result

+----------------------+--------------------------------+
| with_concat          | with_ws                        |
+----------------------+--------------------------------+
| Tel: +91-98100-00001 | Asha | +91-98100-00001 | Delhi |
| NULL                 | Ben | London                   |
+----------------------+--------------------------------+

Case and spaces

sql

SELECT UPPER('mysql') AS up, LOWER('MySQL') AS low, TRIM('   padded   ') AS trimmed, LTRIM('  left') AS left_trim, RTRIM('right  ') AS right_trim;

result

+-------+-------+---------+-----------+------------+
| up    | low   | trimmed | left_trim | right_trim |
+-------+-------+---------+-----------+------------+
| MYSQL | mysql | padded  | left      | right      |
+-------+-------+---------+-----------+------------+

TRIM can remove other characters too: TRIM(BOTH '-' FROM '--title--') returns title. Cleaning user input with TRIM before comparing it is a common job.

How long is it?

sql

SELECT 'café' AS word, CHAR_LENGTH('café') AS characters, LENGTH('café') AS bytes;

result

+-------+------------+-------+
| word  | characters | bytes |
+-------+------------+-------+
| café  |          4 |     5 |
+-------+------------+-------+

CHAR_LENGTH counts characters. LENGTH counts bytes, and in utf8mb4 the accented é takes two bytes. Use CHAR_LENGTH when you want to know how many letters a person sees, such as when checking a maximum title length.

Cutting pieces out

LEFT(text, n) and RIGHT(text, n) take characters from either end. SUBSTRING(text, start, length) takes from the middle. Positions start at 1:

sql

SELECT LEFT('Wireless Mouse', 8) AS l, RIGHT('Wireless Mouse', 5) AS r, SUBSTRING('Wireless Mouse', 10, 5) AS middle, SUBSTRING('Wireless Mouse', 10) AS to_end;

result

+----------+-------+--------+--------+
| l        | r     | middle | to_end |
+----------+-------+--------+--------+
| Wireless | Mouse | Mouse  | Mouse  |
+----------+-------+--------+--------+

A negative start counts from the end: SUBSTRING('Wireless Mouse', -5) returns Mouse.

Finding and replacing

LOCATE(search, text) returns the position of the first match (or 0), and REPLACE swaps every occurrence of a substring:

sql

SELECT LOCATE('@', email) AS at_position, REPLACE(email, '@example.com', '@shop.test') AS new_email FROM customers WHERE id = 1;

result

+-------------+----------------------+
| at_position | new_email            |
+-------------+----------------------+
|          11 | asha.verma@shop.test |
+-------------+----------------------+

A handy function for splitting is SUBSTRING_INDEX(text, delimiter, count). A positive count returns everything before that many delimiters; a negative count returns everything after:

sql

SELECT email, SUBSTRING_INDEX(email, '@', 1) AS username, SUBSTRING_INDEX(email, '@', -1) AS domain FROM customers WHERE id = 3;

result

+--------------------------+--------------+-------------+
| email                    | username     | domain      |
+--------------------------+--------------+-------------+
| chloe.dubois@example.com | chloe.dubois | example.com |
+--------------------------+--------------+-------------+

Padding, repeating and reversing

sql

SELECT LPAD(7, 3, '0') AS padded, REPEAT('ab', 3) AS repeated, REVERSE('MySQL') AS reversed, FORMAT(1234567.891, 2) AS formatted;

result

+--------+----------+----------+--------------+
| padded | repeated | reversed | formatted    |
+--------+----------+----------+--------------+
| 007    | ababab   | LQSyM    | 1,234,567.89 |
+--------+----------+----------+--------------+

LPAD pads on the left, handy for invoice numbers like 007. FORMAT adds thousands separators and rounds, and it returns text, so use it for display only and not for further arithmetic.

Functions in WHERE and updates

You can use functions in a condition:

sql

SELECT first_name FROM customers WHERE CHAR_LENGTH(first_name) = 4 AND LOWER(city) LIKE '%o%';

result

+------------+
| first_name |
+------------+
| Hiro       |
| Jack       |
+------------+

Be careful with large tables. When you wrap a column in a function, as in WHERE LOWER(email) = 'asha@...', MySQL cannot use a normal index on email, because the index holds the original values, not the lower-cased ones. The default collation is already case-insensitive, so you rarely need LOWER for comparisons. Functions in UPDATE are great for bulk cleaning: UPDATE customers SET email = LOWER(TRIM(email));.

Common mistakes

Counting from 0. SUBSTRING('abc', 0, 2) returns an empty string because SQL positions start at 1.
Joining with +. In MySQL 'a' + 'b' converts text to numbers and returns 0. Use CONCAT.
Letting NULL swallow a CONCAT. Use CONCAT_WS or IFNULL.
Using LENGTH for character counts on non-English text.
Wrapping an indexed column in a function inside WHERE.

Exercise

Write a query that shows, for every customer, the full name in capitals, the email domain (the part after @), and the number of characters in the email address. Show only customers whose email domain is example.com, and the first three rows.

Show one possible answer

sql

SELECT UPPER(CONCAT(first_name, ' ', last_name)) AS full_name,
       SUBSTRING_INDEX(email, '@', -1) AS domain,
       CHAR_LENGTH(email) AS email_length
FROM customers
WHERE SUBSTRING_INDEX(email, '@', -1) = 'example.com'
LIMIT 3;

result

+--------------+-------------+--------------+
| full_name    | domain      | email_length |
+--------------+-------------+--------------+
| ASHA VERMA   | example.com |           22 |
| BEN CARTER   | example.com |           22 |
| CHLOE DUBOIS | example.com |           24 |
+--------------+-------------+--------------+

Key points

CONCAT joins text, CONCAT_WS joins with a separator and skips NULLs, and UPPER, LOWER and TRIM tidy values.
LENGTH counts bytes while CHAR_LENGTH counts characters, so they differ for non-English text.
SUBSTRING, LEFT, RIGHT, LOCATE and SUBSTRING_INDEX extract and find parts of a string. Positions start at 1, not 0.
Applying a function to a column in WHERE usually prevents MySQL using an index on that column.

Functions in a query

Joining text

Case and spaces

How long is it?

Cutting pieces out

Finding and replacing

Padding, repeating and reversing

Functions in WHERE and updates

Common mistakes

Exercise

Key points

Check yourself