Forked from duanehutchins/split-string-into-rows.sql
Created
August 24, 2018 13:18
-
-
Save dracken/a56fb6f4173d7fb44a37427e67f35291 to your computer and use it in GitHub Desktop.
MySQL split comma-separated string into rows
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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)); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment