Split String Function in MySQL

November 12, 2010Gokhan Atil2 CommentsMySQLsplit

I saw a question about how to split delimited string in MySQL. MySQL does not include a function to split a delimited string. So I decided to write a simple one:

DELIMITER $$

CREATE FUNCTION split( str VARCHAR(500), delchar VARCHAR(2), x INT )
RETURNS VARCHAR(500)
BEGIN
RETURN SUBSTR(SUBSTRING_INDEX(str, delchar, x),
LENGTH(SUBSTRING_INDEX(str, delchar, x-1))+IF(x > 1, 2, 1));
END$$

DELIMITER ;

DELIMITER $$

CREATE FUNCTION split( str VARCHAR(500), delchar VARCHAR(2), x INT )

RETURNS VARCHAR(500)

BEGIN

RETURN SUBSTR(SUBSTRING_INDEX(str, delchar, x),

LENGTH(SUBSTRING_INDEX(str, delchar, x-1))+IF(x > 1, 2, 1));

END$$

DELIMITER ;

When we call this function as SPLIT( ‘ali,ahmet,mehmet’, ‘,’, 3 ), we get the 3rd item “mehmet”. If we call this function as SPLIT( ‘ali,ahmet,mehmet’, ‘,’, 5 ), it will return an empty string.

Please share

Gokhan Atil

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases, and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

2 Comments

mahesh singh

September 21, 2015 Reply

hey

i have string and i want to split that into string array . I don’t want to use third parameter can i do that without it or how can i do that?

suppose that my input is ‘my name is abc’ and i want to split this string using space(i.e. ‘ ‘) and my output comes that i get each word (my,name,is,abc) and then i want each elements in that array want to compare with another string please let me know how can i do that?
- Gokhan Atil
  
  September 21, 2015
  
  Mahesh, in current version of MySQL, stored functions can’t return cursors. You may check the document.

Leave Comment Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.