Skip to content

Instantly share code, notes, and snippets.

@Querela
Forked from paulochf/strsplit.sql
Created August 12, 2020 20:15
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Querela/997fc294c20cae4f491fd9b58a9831d1 to your computer and use it in GitHub Desktop.
Save Querela/997fc294c20cae4f491fd9b58a9831d1 to your computer and use it in GitHub Desktop.
MySQL split function: get nth splitted term from string separated value
-- Retrieved from http://dev.mysql.com/doc/refman/5.6/en/string-functions.html at 2015-feb-10 18:16
-- Working on MySQL version 5.6.19-0ubuntu0.14.04.1 (Ubuntu)
--
-- Posted by Chris Stubben on August 21 2008 3:49pm
-- Split delimited strings
CREATE FUNCTION
strSplit(x VARCHAR(255), delim VARCHAR(12), pos INT) RETURNS VARCHAR(255)
return
REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1),
delim,
'');
-- Usage: SELECT strSplit("aaa,b,cc,d", ',', n) AS nth;
SELECT strSplit("aaa,b,cc,d", ',', 2) AS second;
+--------+
| second |
+--------+
| b |
+--------+
SELECT strSplit("a|bb|ccc|dd", '|', 3) AS third;
+-------+
| third |
+-------+
| ccc |
+-------+
SELECT strSplit("aaa,b,cc,d", ',', 7) AS 7th;
+------+
| 7th |
+------+
| NULL |
+------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment