MySQL Split String and Clean Text: SUBSTRING

Real data is messy: names with extra spaces, tags stored as 'a,b,c', phone numbers in five formats. These tools help you clean and split text.

Splitting at a delimiter

query.sql

SQL

SELECT SUBSTRING_INDEX('red,green,blue', ',', 1)  AS first_item,
       SUBSTRING_INDEX('red,green,blue', ',', 2)  AS first_two,
       SUBSTRING_INDEX('red,green,blue', ',', -1) AS last_item,
       SUBSTRING_INDEX(SUBSTRING_INDEX('red,green,blue', ',', 2), ',', -1) AS second_item;

Output

OUTPUT

+------------+-----------+-----------+-------------+
| first_item | first_two | last_item | second_item |
+------------+-----------+-----------+-------------+
| red        | red,green | blue      | green       |
+------------+-----------+-----------+-------------+
1 row in set

To get the n-th item, take everything up to the n-th delimiter, then take the last part of that.

Turning a list into rows

MySQL has no split function that returns rows. You can build one with a number table and SUBSTRING_INDEX:

query.sql

SQL

CREATE TABLE articles (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(30), tags VARCHAR(100));
INSERT INTO articles (title, tags) VALUES ('MySQL basics', 'sql,mysql,beginner'), ('PHP and MySQL', 'php,mysql'), ('Indexes', 'mysql,performance,sql');

CREATE TABLE numbers (n INT PRIMARY KEY);
INSERT INTO numbers VALUES (1), (2), (3), (4), (5);

SELECT a.id, a.title, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(a.tags, ',', numbers.n), ',', -1)) AS tag
FROM articles a
JOIN numbers ON numbers.n <= 1 + CHAR_LENGTH(a.tags) - CHAR_LENGTH(REPLACE(a.tags, ',', ''))
ORDER BY a.id, numbers.n;

Output

OUTPUT

+----+---------------+-------------+
| id | title         | tag         |
+----+---------------+-------------+
|  1 | MySQL basics  | sql         |
|  1 | MySQL basics  | mysql       |
|  1 | MySQL basics  | beginner    |
|  2 | PHP and MySQL | php         |
|  2 | PHP and MySQL | mysql       |
|  3 | Indexes       | mysql       |
|  3 | Indexes       | performance |
|  3 | Indexes       | sql         |
+----+---------------+-------------+
8 rows in set

The join condition counts the commas (length - length without commas), so each article is repeated once per tag. This works, but it is an emergency tool. In MySQL 8 you can also use JSON_TABLE to split a list (JSON_TABLE).

Searching a comma-separated list

FIND_IN_SET(item, list) returns the position of an item in a comma-separated list, or 0:

query.sql

SQL

SELECT title FROM articles WHERE FIND_IN_SET('sql', tags) > 0 ORDER BY id;

Output

OUTPUT

+--------------+
| title        |
+--------------+
| MySQL basics |
| Indexes      |
+--------------+
2 rows in set

It works, but the query cannot use an index, and there is no integrity (a typo such as sql with a space is a different tag).

Why lists in columns are a design smell

Storing 'sql,mysql,beginner' in one column breaks the rule “one fact per column” (Normalization). Counting articles per tag, renaming a tag, or finding all tags needs string tricks. The proper design is a tags table and a link table (Many-to-Many):

query.sql

SQL

CREATE TABLE article_tags (article_id INT NOT NULL, tag VARCHAR(30) NOT NULL, PRIMARY KEY (article_id, tag));
INSERT INTO article_tags (article_id, tag)
SELECT a.id, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(a.tags, ',', numbers.n), ',', -1))
FROM articles a JOIN numbers ON numbers.n <= 1 + CHAR_LENGTH(a.tags) - CHAR_LENGTH(REPLACE(a.tags, ',', ''));

SELECT tag, COUNT(*) AS articles FROM article_tags GROUP BY tag ORDER BY articles DESC, tag;

Output

OUTPUT

+-------------+----------+
| tag         | articles |
+-------------+----------+
| mysql       |        3 |
| sql         |        2 |
| beginner    |        1 |
| performance |        1 |
| php         |        1 |
+-------------+----------+
5 rows in set

Once the data is in rows, counting, indexing and joining are normal SQL.

Cleaning data

query.sql

SQL

SELECT TRIM(REPLACE('  Asha   Rao ', '  ', ' '))                    AS one_pass_is_not_enough,
       REGEXP_REPLACE('  Asha   Rao ', '\\s+', ' ')                 AS regex_collapse,
       TRIM(REGEXP_REPLACE('  Asha   Rao ', '\\s+', ' '))           AS cleaned,
       REGEXP_REPLACE('+91 (98450) 12-345', '[^0-9+]', '')           AS phone_digits,
       REPLACE(REPLACE('a-b_c', '-', ' '), '_', ' ')                AS two_replaces;

Output

OUTPUT

+------------------------+----------------+----------+---------------+--------------+
| one_pass_is_not_enough | regex_collapse | cleaned  | phone_digits  | two_replaces |
+------------------------+----------------+----------+---------------+--------------+
| Asha  Rao              |  Asha Rao      | Asha Rao | +919845012345 | a b c        |
+------------------------+----------------+----------+---------------+--------------+
1 row in set

REGEXP_REPLACE (MySQL 8.0 and later) is the right tool for patterns: collapse any run of whitespace into one space, strip non-digits and so on.

Soundex and similarity

query.sql

SQL

SELECT SOUNDEX('Smith') AS smith, SOUNDEX('Smyth') AS smyth, 'Smith' SOUNDS LIKE 'Smyth' AS sounds_alike;

Output

OUTPUT

+-------+-------+--------------+
| smith | smyth | sounds_alike |
+-------+-------+--------------+
| S530  | S530  |            1 |
+-------+-------+--------------+
1 row in set

SOUNDEX produces a code for how English words sound, and SOUNDS LIKE compares two codes. It is crude, and limited to English. For real fuzzy search, use a full-text index or an external search engine.

Cleaning on the way in

The best place to clean text is before it is stored: trim and lower-case emails, normalise phone numbers, validate formats in the application, and enforce rules with constraints. A clean table needs no cleaning functions in every query.

lightbulb

Preview, then update

Write the cleaning expression in a SELECT old_value, new_value, check a sample of rows, and only then use it in an UPDATE (Safe UPDATE).