MySQL String Functions: CONCAT, SUBSTRING, TRIM, REPLACE

Text is everywhere, and MySQL has many functions to work with it.

Joining: CONCAT and CONCAT_WS

query.sql

SQL

SELECT CONCAT('Maya', ' ', 'Shah') AS full_name,
       CONCAT_WS(', ', 'Pune', 'Maharashtra', 'India') AS address,
       CONCAT('Total: ', 42) AS with_number,
       CONCAT('Note: ', NULL) AS with_null,
       CONCAT_WS(' ', 'Maya', NULL, 'Shah') AS ws_skips_null;

Output

OUTPUT

+-----------+--------------------------+-------------+-----------+---------------+
| full_name | address                  | with_number | with_null | ws_skips_null |
+-----------+--------------------------+-------------+-----------+---------------+
| Maya Shah | Pune, Maharashtra, India | Total: 42   | NULL      | Maya Shah     |
+-----------+--------------------------+-------------+-----------+---------------+
1 row in set

CONCAT returns NULL if any argument is NULL. CONCAT_WS(separator, ...) (“with separator”) skips NULLs, which makes it the best choice for names and addresses where some parts may be missing. Remember that + does not join text in MySQL.

Length: bytes vs characters

query.sql

SQL

SELECT LENGTH('hello') AS bytes_ascii, LENGTH('héllo') AS bytes_accent, CHAR_LENGTH('héllo') AS characters, BIT_LENGTH('a') AS bits;

Output

OUTPUT

+-------------+--------------+------------+------+
| bytes_ascii | bytes_accent | characters | bits |
+-------------+--------------+------------+------+
|           5 |            6 |          5 |    8 |
+-------------+--------------+------------+------+
1 row in set

LENGTH counts bytes, CHAR_LENGTH counts characters. For text in utf8mb4, always use CHAR_LENGTH when you mean “number of characters”.

Case

query.sql

SQL

SELECT UPPER('mysql') AS upper_case, LOWER('MySQL') AS lower_case, UCASE('x') AS same_as_upper;

Output

OUTPUT

+------------+------------+---------------+
| upper_case | lower_case | same_as_upper |
+------------+------------+---------------+
| MYSQL      | mysql      | X             |
+------------+------------+---------------+
1 row in set

MySQL has no built-in INITCAP (capitalise each word). Build it in the application, or with a small trick for single words: CONCAT(UPPER(LEFT(s, 1)), LOWER(SUBSTRING(s, 2))).

Trimming

query.sql

SQL

SELECT CONCAT('[', TRIM('   padded   '), ']') AS trimmed,
       CONCAT('[', LTRIM('   padded   '), ']') AS left_trimmed,
       CONCAT('[', RTRIM('   padded   '), ']') AS right_trimmed,
       TRIM(BOTH '*' FROM '***star***') AS remove_stars,
       TRIM(LEADING '0' FROM '000123') AS remove_leading_zeros;

Output

OUTPUT

+----------+--------------+---------------+--------------+----------------------+
| trimmed  | left_trimmed | right_trimmed | remove_stars | remove_leading_zeros |
+----------+--------------+---------------+--------------+----------------------+
| [padded] | [padded   ]  | [   padded]   | star         | 123                  |
+----------+--------------+---------------+--------------+----------------------+
1 row in set

TRIM removes spaces by default, or any character you name. Trim data when it enters the database, so you do not need to trim in every query.

Parts of a string

query.sql

SQL

SELECT SUBSTRING('Hello, world', 1, 5)   AS first_five,
       SUBSTRING('Hello, world', 8)       AS from_position_8,
       SUBSTRING('Hello, world', -5)      AS last_five,
       LEFT('Hello, world', 5)            AS left_5,
       RIGHT('Hello, world', 5)           AS right_5,
       MID('Hello', 2, 3)                 AS mid_is_substring;

Output

OUTPUT

+------------+-----------------+-----------+--------+---------+------------------+
| first_five | from_position_8 | last_five | left_5 | right_5 | mid_is_substring |
+------------+-----------------+-----------+--------+---------+------------------+
| Hello      | world           | world     | Hello  | world   | ell              |
+------------+-----------------+-----------+--------+---------+------------------+
1 row in set

Positions start at 1, not 0. A negative start counts from the end.

Searching inside a string

query.sql

SQL

SELECT LOCATE('lo', 'Hello, world')   AS lo_position,
       LOCATE('xyz', 'Hello, world')  AS not_found,
       INSTR('Hello, world', 'o')      AS first_o,
       LOCATE('o', 'Hello, world', 6)  AS o_after_position_6;

Output

OUTPUT

+-------------+-----------+---------+--------------------+
| lo_position | not_found | first_o | o_after_position_6 |
+-------------+-----------+---------+--------------------+
|           4 |         0 |       5 |                  9 |
+-------------+-----------+---------+--------------------+
1 row in set

LOCATE and INSTR return the position of the first match, or 0 if there is none (not NULL and not -1).

Replacing and repeating

query.sql

SQL

SELECT REPLACE('2026-07-15', '-', '/') AS replaced,
       REPEAT('ab', 3)                  AS repeated,
       REVERSE('MySQL')                 AS reversed,
       INSERT('Hello world', 7, 5, 'MySQL') AS inserted,
       SPACE(3) = '   '                 AS three_spaces;

Output

OUTPUT

+------------+----------+----------+-------------+--------------+
| replaced   | repeated | reversed | inserted    | three_spaces |
+------------+----------+----------+-------------+--------------+
| 2026/07/15 | ababab   | LQSyM    | Hello MySQL |            1 |
+------------+----------+----------+-------------+--------------+
1 row in set

REPLACE is case-sensitive for binary strings and follows the collation otherwise. It replaces all matches.

Padding

query.sql

SQL

1
2
3

SELECT LPAD('42', 6, '0')  AS invoice_number,
       RPAD('Total', 10, '.') AS leader,
       LPAD('toolong', 3, '*') AS truncated;

Output

OUTPUT

+----------------+------------+-----------+
| invoice_number | leader     | truncated |
+----------------+------------+-----------+
| 000042         | Total..... | too       |
+----------------+------------+-----------+
1 row in set

LPAD is the usual way to format codes with leading zeros. If the text is longer than the target length, it is cut, as in the last column.

Comparing

query.sql

SQL

SELECT STRCMP('apple', 'banana') AS apple_vs_banana,
       STRCMP('b', 'a')           AS b_vs_a,
       STRCMP('a', 'A')           AS equal_ignoring_case,
       'apple' < 'banana'         AS operator_form;

Output

OUTPUT

+-----------------+--------+---------------------+---------------+
| apple_vs_banana | b_vs_a | equal_ignoring_case | operator_form |
+-----------------+--------+---------------------+---------------+
|              -1 |      1 |                   0 |             1 |
+-----------------+--------+---------------------+---------------+
1 row in set

Useful for real tasks

query.sql

SQL

SELECT email,
       SUBSTRING_INDEX(email, '@', 1)  AS username,
       SUBSTRING_INDEX(email, '@', -1) AS domain,
       LOWER(TRIM(email))               AS cleaned
FROM (SELECT '  Asha.Rao@Example.com ' AS email) AS t;

Output

OUTPUT

+-------------------------+------------+--------------+----------------------+
| email                   | username   | domain       | cleaned              |
+-------------------------+------------+--------------+----------------------+
|   Asha.Rao@Example.com  |   Asha.Rao | Example.com  | asha.rao@example.com |
+-------------------------+------------+--------------+----------------------+
1 row in set

SUBSTRING_INDEX(text, delimiter, n) returns the part before the n-th delimiter (or after it, if n is negative). See Splitting and Cleaning Text.

Summary table

Task	Function
Join	`CONCAT`, `CONCAT_WS`
Length	`CHAR_LENGTH` (characters), `LENGTH` (bytes)
Case	`UPPER`, `LOWER`
Trim	`TRIM`, `LTRIM`, `RTRIM`
Cut	`SUBSTRING`, `LEFT`, `RIGHT`, `SUBSTRING_INDEX`
Find	`LOCATE`, `INSTR`, `FIND_IN_SET`
Change	`REPLACE`, `INSERT`, `REGEXP_REPLACE`
Pad	`LPAD`, `RPAD`

warning

Functions in WHERE

WHERE LOWER(email) = 'x' and WHERE LEFT(name, 3) = 'Abc' cannot use an index on the column. With a case-insensitive collation you do not need LOWER, and name LIKE 'Abc%' can use the index.