Skip to content

Instantly share code, notes, and snippets.

@Sei-color

Sei-color/split-string-into-rows.sql

Forked from duanehutchins/split-string-into-rows.sql
Created February 28, 2023 14:46
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Sei-color/e268efd2ac6cc64ae98d8c77b95b4f56 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Sei-color/e268efd2ac6cc64ae98d8c77b95b4f56 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @duanehutchins duanehutchins revised this gist Mar 3, 2021. 1 changed file with 42 additions and 28 deletions.
    70 changes: 42 additions & 28 deletions split-string-into-rows.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -4,37 +4,48 @@
    -- Split a string into a mysql resultset of rows
    -- This is designed to work with a comma-separated string (csv, SET, array)

    -- To use a delimiter other than a comma:
    -- Just change all the occurrences of ',' to the new delimiter
    -- (four occurrences in SET_EXTRACT and one occurrence in SET_COUNT)

    -- 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)
    DROP FUNCTION SET_EXTRACT;
    CREATE FUNCTION SET_EXTRACT($i SMALLINT UNSIGNED, $strlist MEDIUMBLOB) 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)
    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
    DROP FUNCTION SET_COUNT;
    CREATE FUNCTION SET_COUNT($strlist MEDIUMBLOB) 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` (
    -- The ENGINE=MEMORY engine may be used for a performance gain, but see note on the MEMORY engine listed below
    DROP TABLE `number_set`;
    CREATE TABLE `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;
    ) ENGINE=INNODB DEFAULT CHARSET=BINARY MAX_ROWS=256 MIN_ROWS=256;

    -- Note: If using MEMORY engine for the number_set table, the data in MEMORY tables is lost on server restart,
    -- I recommend adding this INSERT query below to the mysql --init-file, if using MEMORY engine
    -- https://dev.mysql.com/doc/refman/5.7/en/memory-storage-engine.html#memory-storage-engine-loading-data

    -- 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
    TRUNCATE number_set;
    INSERT INTO number_set (n)
    SELECT n1.n+(n2.n<<2)+(n3.n<<4)+(n4.n<<6) AS n FROM
    SELECT STRAIGHT_JOIN 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,
    @@ -44,37 +55,40 @@ INSERT INTO number_set (n)
    -- 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
    DROP FUNCTION split_string_into_rows;
    CREATE FUNCTION split_string_into_rows($split_string_into_rows MEDIUMBLOB) RETURNS MEDIUMBLOB
    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);
    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:
    -- 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');
    -- As a single query:
    -- SELECT e FROM split_string_into_rows WHERE split_string_into_rows('foo,bar,foobar,barfoo');
    -- With a JOIN to another table:
    -- SELECT u.name FROM users u 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');
    -- 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,June');
    -- 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));
    SELECT HIGH_PRIORITY SQL_SMALL_RESULT n1.n AS i, SET_EXTRACT(n1.n, split_string_into_rows(NULL)) AS e
    FROM number_set n1
    WHERE 1&(n1.n < SET_COUNT(split_string_into_rows(NULL)));

    -- Limited to 65535 results (slightly slower)
    CREATE OR REPLACE VIEW split_string_into_rows(i,e) AS
    SELECT STRAIGHT_JOIN n1.n|(n256.n<<8) AS i, SET_EXTRACT(n1.n|(n256.n<<8), split_string_into_rows(NULL)) AS e
    FROM number_set n1, number_set n256
    WHERE 1&(n1.n|(n256.n<<8) < SET_COUNT(split_string_into_rows(NULL)));

    -- Larger than 65535 results will get very slow,
    -- but can be done with additional joins within the above view
    -- and adjusting the INT and BLOB variable types to support larger sizes in the functions
  2. @duanehutchins duanehutchins revised this gist Feb 21, 2018. 1 changed file with 5 additions and 3 deletions.
    8 changes: 5 additions & 3 deletions split-string-into-rows.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -55,11 +55,13 @@ CREATE FUNCTION split_string_into_rows($split_string_into_rows BLOB) RETURNS BLO
    -- 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;
    -- 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');
    -- JOIN to another table:
    -- SELECT u.name AS spring_births FROM users u JOIN split_string_into_rows s ON u.birth_month = s.substr WHERE split_string_into_rows('March,April,May');
    -- 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.

  3. @duanehutchins duanehutchins revised this gist Feb 21, 2018. 1 changed file with 21 additions and 18 deletions.
    39 changes: 21 additions & 18 deletions split-string-into-rows.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -11,21 +11,23 @@
    -- 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)
    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,',',''))
    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
    `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
    @@ -42,9 +44,9 @@ INSERT INTO number_set (n)
    -- 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 SQL SECURITY INVOKER
    RETURN IF($split_string_into_rows IS NULL, IFNULL(@split_string_into_rows,""), "1"|@split_string_into_rows:=$split_string_into_rows);
    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
    @@ -62,14 +64,15 @@ CREATE FUNCTION split_string_into_rows($split_string_into_rows BLOB) RETURNS BLO
    -- This preforms much faster than FIND_IN_SET() because the indexes are preserved.

    -- Limited to 256 results
    CREATE OR REPLACE ALGORITHM = MERGE SQL SECURITY INVOKER VIEW split_string_into_rows(i,e) AS
    SELECT HIGH_PRIORITY n1.n AS i, SET_EXTRACT(n1.n,split_string_into_rows(NULL)) AS e
    FROM number_set n1
    WHERE n1.n < SET_COUNT(split_string_into_rows(NULL));
    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 SQL SECURITY INVOKER VIEW split_string_into_rows(i,e) AS
    SELECT HIGH_PRIORITY n1.n+(n2.n<<8) AS i, SET_EXTRACT(n1.n+(n2.n<<8),split_string_into_rows(NULL)) AS e
    FROM number_set n1
    STRAIGHT_JOIN number_set n2 ON n2.n <= ((SET_COUNT(split_string_into_rows(NULL))-1)>>8)
    WHERE n1.n < SET_COUNT(split_string_into_rows(NULL)) AND n1.n+(n2.n<<8) < SET_COUNT(split_string_into_rows(NULL));
    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));
  4. @duanehutchins duanehutchins revised this gist Feb 21, 2018. 1 changed file with 11 additions and 4 deletions.
    15 changes: 11 additions & 4 deletions split-string-into-rows.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -60,9 +60,16 @@ CREATE FUNCTION split_string_into_rows($split_string_into_rows BLOB) RETURNS BLO
    -- SELECT u.name AS spring_births FROM users u JOIN split_string_into_rows s ON u.birth_month = s.substr WHERE 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 65536 results but more can be added with additional joins to number_set

    -- Limited to 256 results
    CREATE OR REPLACE ALGORITHM = MERGE SQL SECURITY INVOKER VIEW split_string_into_rows(i,e) AS
    SELECT HIGH_PRIORITY n1.n AS i, SET_EXTRACT(n1.n,split_string_into_rows(NULL)) AS e
    FROM number_set n1
    WHERE n1.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 SQL SECURITY INVOKER VIEW split_string_into_rows(i,e) AS
    SELECT HIGH_PRIORITY SQL_SMALL_RESULT n1.n+IFNULL(n2.n<<8,0) AS i, SET_EXTRACT(n1.n+IFNULL(n2.n<<8,0),split_string_into_rows(NULL)) AS e
    SELECT HIGH_PRIORITY n1.n+(n2.n<<8) AS i, SET_EXTRACT(n1.n+(n2.n<<8),split_string_into_rows(NULL)) AS e
    FROM number_set n1
    LEFT JOIN number_set n2 ON(SET_COUNT(split_string_into_rows(NULL))>256 AND n2.n<=((SET_COUNT(split_string_into_rows(NULL))-1)>>8) AND n1.n+(n2.n<<8)<SET_COUNT(split_string_into_rows(NULL)))
    WHERE n1.n<SET_COUNT(split_string_into_rows(NULL));
    STRAIGHT_JOIN number_set n2 ON n2.n <= ((SET_COUNT(split_string_into_rows(NULL))-1)>>8)
    WHERE n1.n < SET_COUNT(split_string_into_rows(NULL)) AND n1.n+(n2.n<<8) < SET_COUNT(split_string_into_rows(NULL));
  5. @duanehutchins duanehutchins revised this gist Feb 21, 2018. 1 changed file with 12 additions and 8 deletions.
    20 changes: 12 additions & 8 deletions split-string-into-rows.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -16,19 +16,23 @@ CREATE FUNCTION SET_EXTRACT($i SMALLINT UNSIGNED, $strlist BLOB) RETURNS VARBINA

    -- 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 the table (or mysql result) column into rows
    -- 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 (`n`)
    ) ENGINE=MEMORY;
    `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) FROM
    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,
    @@ -58,7 +62,7 @@ CREATE FUNCTION split_string_into_rows($split_string_into_rows BLOB) RETURNS BLO
    -- This preforms much faster than FIND_IN_SET() because the indexes are preserved.
    -- Limited to 65536 results but more can be added with additional joins to number_set
    CREATE OR REPLACE ALGORITHM = MERGE SQL SECURITY INVOKER VIEW split_string_into_rows(i,e) AS
    SELECT n1.n+(n2.n<<8) AS i, SET_EXTRACT(n1.n+(n2.n<<8),split_string_into_rows(NULL)) AS e
    SELECT HIGH_PRIORITY SQL_SMALL_RESULT n1.n+IFNULL(n2.n<<8,0) AS i, SET_EXTRACT(n1.n+IFNULL(n2.n<<8,0),split_string_into_rows(NULL)) AS e
    FROM number_set n1
    JOIN number_set n2 ON(n2.n<=((SET_COUNT(split_string_into_rows(NULL))-1)>>8))
    WHERE n1.n+(n2.n<<8)<SET_COUNT(split_string_into_rows(NULL));
    LEFT JOIN number_set n2 ON(SET_COUNT(split_string_into_rows(NULL))>256 AND n2.n<=((SET_COUNT(split_string_into_rows(NULL))-1)>>8) AND n1.n+(n2.n<<8)<SET_COUNT(split_string_into_rows(NULL)))
    WHERE n1.n<SET_COUNT(split_string_into_rows(NULL));
  6. @duanehutchins duanehutchins revised this gist Jan 18, 2018. 1 changed file with 39 additions and 20 deletions.
    59 changes: 39 additions & 20 deletions split-string-into-rows.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -4,42 +4,61 @@
    -- 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($strlist TEXT, $i INT) = str VARCHAR
    -- 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('foo,bar,foobar',2); // "foobar"
    CREATE FUNCTION SET_EXTRACT($strlist TEXT, $i INT) returns VARCHAR(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);
    -- 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)

    -- View view_number_set
    -- Result-set with a column of integers counting from 0 to 10
    -- Function SET_COUNT
    -- Returns the number of elements in a set
    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 the table (or mysql result) column into rows
    CREATE OR REPLACE VIEW view_number_set AS SELECT 0 AS num UNION SELECT 1 union SELECT 2 union SELECT 3 union SELECT 4 union SELECT 5 union SELECT 6 union SELECT 7 union SELECT 8 union SELECT 9;
    CREATE TABLE IF NOT EXISTS `number_set` (
    `n` tinyint(3) unsigned NOT NULL,
    PRIMARY KEY (`n`)
    ) ENGINE=MEMORY;

    INSERT INTO number_set (n)
    SELECT n1.n+(n2.n<<2)+(n3.n<<4)+(n4.n<<6) 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() = @split_string_into_rows TEXT
    -- Only used as a work-around to pass @split_string_into_rows to the below VIEW
    -- This would not be needed if MySQL allowed user-variables in views
    CREATE FUNCTION split_string_into_rows() RETURNS TEXT DETERMINISTIC NO SQL RETURN @split_string_into_rows;
    -- 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 SQL SECURITY INVOKER
    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 substring (`substr`)
    -- 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 substr FROM split_string_into_rows;
    -- SET @split_string_into_rows:='foo,bar,foobar'; SELECT e FROM split_string_into_rows;
    -- As a single query:
    -- SELECT substr FROM (SELECT @split_string_into_rows:='foo,bar,foobar')x JOIN split_string_into_rows;
    -- SELECT e FROM split_string_into_rows WHERE split_string_into_rows('foo,bar,foobar');
    -- JOIN to another table:
    -- SELECT u.name AS spring_births FROM (SELECT @split_string_into_rows:='March,April,May')x JOIN users u JOIN split_string_into_rows s ON u.birth_month = s.substr;
    -- SELECT u.name AS spring_births FROM users u JOIN split_string_into_rows s ON u.birth_month = s.substr WHERE 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 1000 results but more can be added with additional view_number_set's
    CREATE OR REPLACE VIEW split_string_into_rows AS
    SELECT numset1.num+numset10.num*10+numset100.num*100 AS i, SET_EXTRACT(split_string_into_rows(),numset1.num+numset10.num*10+numset100.num*100) AS substr FROM
    view_number_set numset1, view_number_set numset10, view_number_set numset100
    WHERE numset1.num+numset10.num*10+numset100.num*100 < (1 + CHAR_LENGTH(split_string_into_rows())-LENGTH(REPLACE(split_string_into_rows(),',','')));
    -- Limited to 65536 results but more can be added with additional joins to number_set
    CREATE OR REPLACE ALGORITHM = MERGE SQL SECURITY INVOKER VIEW split_string_into_rows(i,e) AS
    SELECT n1.n+(n2.n<<8) AS i, SET_EXTRACT(n1.n+(n2.n<<8),split_string_into_rows(NULL)) AS e
    FROM number_set n1
    JOIN number_set n2 ON(n2.n<=((SET_COUNT(split_string_into_rows(NULL))-1)>>8))
    WHERE n1.n+(n2.n<<8)<SET_COUNT(split_string_into_rows(NULL));
  7. @duanehutchins duanehutchins created this gist Mar 17, 2017.
    45 changes: 45 additions & 0 deletions split-string-into-rows.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,45 @@
    -- 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($strlist TEXT, $i INT) = str VARCHAR
    -- 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('foo,bar,foobar',2); // "foobar"
    CREATE FUNCTION SET_EXTRACT($strlist TEXT, $i INT) returns VARCHAR(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);

    -- View view_number_set
    -- Result-set with a column of integers counting from 0 to 10
    -- This is a handy tool to pivot the table (or mysql result) column into rows
    CREATE OR REPLACE VIEW view_number_set AS SELECT 0 AS num UNION SELECT 1 union SELECT 2 union SELECT 3 union SELECT 4 union SELECT 5 union SELECT 6 union SELECT 7 union SELECT 8 union SELECT 9;

    -- Function split_string_into_rows() = @split_string_into_rows TEXT
    -- Only used as a work-around to pass @split_string_into_rows to the below VIEW
    -- This would not be needed if MySQL allowed user-variables in views
    CREATE FUNCTION split_string_into_rows() RETURNS TEXT DETERMINISTIC NO SQL RETURN @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 substring (`substr`)
    -- 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 substr FROM split_string_into_rows;
    -- As a single query:
    -- SELECT substr FROM (SELECT @split_string_into_rows:='foo,bar,foobar')x JOIN split_string_into_rows;
    -- JOIN to another table:
    -- SELECT u.name AS spring_births FROM (SELECT @split_string_into_rows:='March,April,May')x JOIN users u JOIN split_string_into_rows s ON u.birth_month = s.substr;
    -- 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 1000 results but more can be added with additional view_number_set's
    CREATE OR REPLACE VIEW split_string_into_rows AS
    SELECT numset1.num+numset10.num*10+numset100.num*100 AS i, SET_EXTRACT(split_string_into_rows(),numset1.num+numset10.num*10+numset100.num*100) AS substr FROM
    view_number_set numset1, view_number_set numset10, view_number_set numset100
    WHERE numset1.num+numset10.num*10+numset100.num*100 < (1 + CHAR_LENGTH(split_string_into_rows())-LENGTH(REPLACE(split_string_into_rows(),',','')));