Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL split comma-separated string into rows
-- split-string-into-rows.sql
-- Duane Hutchins
-- https://www.github.com/duanehutchins
-- Split a string into a mysql resultset of rows
-- This is designed to work with a comma-separated string (csv, SET, array)
-- Function SET_EXTRACT
-- Essentially does the reverse of MySQL's built-in function FIND_IN_SET(str,strlist) = index INT
-- Splits a comma-separated string (AKA "SET"), $strlist, and returns the element (aka substring) matching the provided index, $i.
-- If index $i is zero or positive, the elements are counted from the left, starting at zero.
-- If index $i is negative, the elements are instead counted from the right, starting at -1.
-- If either parameter is NULL or if $i is outside the element count, NULL will be returned
-- Usage Example: SELECT SET_EXTRACT(2,'foo,bar,foobar'); // "foobar"
CREATE FUNCTION SET_EXTRACT($i SMALLINT UNSIGNED, $strlist BLOB) RETURNS VARBINARY(255)
DETERMINISTIC NO SQL
RETURN NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(0b0, ',', $strlist, ",", 0b0), ",", $i+1.5*(SIGN($i+0.5)+1)-1), ',', -SIGN($i+0.5)),0b0)
-- Function SET_COUNT
-- Returns the number of elements in a set
-- (Actually returns the one plus the number of commas in the string)
CREATE FUNCTION SET_COUNT($strlist BLOB) RETURNS SMALLINT UNSIGNED
DETERMINISTIC NO SQL
RETURN 1+CHAR_LENGTH($strlist)-CHAR_LENGTH(REPLACE($strlist,',',''));
-- Table number_set
-- A column of integers counting from 0 to 255
-- This is a handy tool to pivot a table (or mysql result) row of columns into a column of rows
CREATE TABLE IF NOT EXISTS `number_set` (
`n` TINYINT(3) UNSIGNED NOT NULL PRIMARY KEY,
UNIQUE KEY `n` (`n`) USING BTREE
) ENGINE=MEMORY DEFAULT CHARSET=BINARY MAX_ROWS=256 MIN_ROWS=256;
-- Insert numbers 0-255 into the number_set table
-- The data in MEMORY tables is lost on server restart, so I recommend adding this to the mysql --init-file
-- https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_init-file
INSERT INTO number_set (n)
SELECT n1.n+(n2.n<<2)+(n3.n<<4)+(n4.n<<6) AS n FROM
(SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) n1,
(SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) n2,
(SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) n3,
(SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) n4;
-- Function split_string_into_rows()
-- Only used as a work-around to pass @split_string_into_rows to the split_string_into_rows VIEW
-- Returns @split_string_into_rows if the argument is NULL
-- Sets @split_string_into_rows if the argument is not NULL
CREATE FUNCTION split_string_into_rows($split_string_into_rows BLOB) RETURNS BLOB
DETERMINISTIC NO SQL
RETURN IF($split_string_into_rows IS NULL, IFNULL(@split_string_into_rows,""), "1"|@split_string_into_rows:=$split_string_into_rows);
-- View split_string_into_rows
-- Splits a comma-delimited string (aka csv aka comma-separated string) into rows
-- Result set contains the index (`i`) and element (`e`)
-- Resultset sorted by index, starting at zero
-- The comma-separated string is passed via @split_string_into_rows
-- Usage Examples:
-- Two queries:
-- SET @split_string_into_rows = 'foo,bar,foobar'; SELECT e FROM split_string_into_rows;
-- As a single query:
-- SELECT e FROM split_string_into_rows WHERE split_string_into_rows('foo,bar,foobar');
-- With a JOIN to another table:
-- SELECT u.name FROM users JOIN split_string_into_rows s ON u.birth_month = s.e WHERE split_string_into_rows('March,April,May');
-- _ or even better _
-- SELECT STRAIGHT_JOIN u.name FROM split_string_into_rows s, users u WHERE u.birth_month = s.e AND split_string_into_rows('March,April,May');
-- Field indexes are still used when doing a join against a string split!
-- This preforms much faster than FIND_IN_SET() because the indexes are preserved.
-- Limited to 256 results
CREATE OR REPLACE ALGORITHM = MERGE VIEW split_string_into_rows(i,e) AS
SELECT HIGH_PRIORITY SQL_SMALL_RESULT split_string_into_rows.n AS i, SET_EXTRACT(split_string_into_rows.n,split_string_into_rows(NULL)) AS e
FROM number_set split_string_into_rows
WHERE split_string_into_rows.n < SET_COUNT(split_string_into_rows(NULL));
-- Limited to 65536 results. This limit is increased with additional joins to number_set
CREATE OR REPLACE ALGORITHM = MERGE VIEW split_string_into_rows(i,e) AS
SELECT HIGH_PRIORITY STRAIGHT_JOIN split_string_into_rows_1.n+(split_string_into_rows_2.n<<8) AS i, SET_EXTRACT(split_string_into_rows_1.n+(split_string_into_rows_2.n<<8),split_string_into_rows(NULL)) AS e
FROM number_set split_string_into_rows_1, number_set split_string_into_rows_2
WHERE split_string_into_rows_1.n < SET_COUNT(split_string_into_rows(NULL))
AND split_string_into_rows_2.n <= (SET_COUNT(split_string_into_rows(NULL))-1)>>8
AND split_string_into_rows_1.n+(split_string_into_rows_2.n<<8) < SET_COUNT(split_string_into_rows(NULL));
@duanehutchins

This comment has been minimized.

Copy link
Owner Author

commented Mar 17, 2017

Extract an element from a comma-separated string.

mysql> SELECT SET_EXTRACT(2,'foo,bar,foobar') as e;
+--------+
| e      |
+--------+
| foobar |
+--------+

Split a string into a mysql resultset of rows.

mysql> SELECT * FROM split_string_into_rows WHERE split_string_into_rows('foo,bar,foobar');
+---+--------+
| i | e      |
+---+--------+
| 0 | foo    |
| 1 | bar    |
| 2 | foobar |
+---+--------+

Even join string-split resultset to another table while preserving index lookups! Much faster than FIND_IN_SET().

mysql> SELECT u.name AS spring_births FROM users u JOIN split_string_into_rows s ON u.birth_month = s.e WHERE split_string_into_rows('March,April,May');
+---------------+
| spring_births |
+---------------+
| John          |
| Duane         |
| Dave          |
+---------------+
@rseshadri

This comment has been minimized.

Copy link

commented Jun 4, 2018

In the "WHERE split_string_into_rows('March,April,May');" when I try to Pass a table column like "WHERE split_string_into_rows(ca.answer);"
where ca.answer is a comma seperated string, it does not get the results correctly. Looks like it stops after taking the value after first comma

@ward3r

This comment has been minimized.

Copy link

commented Jun 29, 2018

Hi! Thanks for a great script!
Please tell how to do additional joins to number_set to overcome 65536 limit?

@e7

This comment has been minimized.

Copy link

commented Sep 4, 2018

great job, but I got this

mysql> SELECT * FROM split_string_into_rows WHERE split_string_into_rows('34,24,67');
+---+------+
| i | e |
+---+------+
| 0 | 34 |
| 1 | 24 |
+---+------+
2 rows in set (0.00 sec)

@hurelhuyag

This comment has been minimized.

Copy link

commented Jan 21, 2019

How can I use different delimiter instead of comma?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.