Is there something analogous to a split() method in mySql?

Question

I'm looking to write a stored procedure that takes as a parameter a string that's delimited by a token and then to run a while loop in the procedure for every item in that string.

I'm not seeing anything in the mysql documentation that would accomplish this task... is there a way to do this?

Answer 1

I think you'll need to roll your own function. substring_index will come in handy:

SUBSTRING_INDEX(str,delim,count)

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

This function is multi-byte safe.

Answer 2

I think you can accomplish what you want via substring_index

It's not as straightforward as just a split() function but I'm sure you can work out a while loop with this function.

Answer 3

Unfortunately, mysql doesn't let functions return arrays or tables (that I know of) so you have to do this a bit hackily.

Here's a sample stored proc:

DELIMITER $$

create function splitter_count (str varchar(200), delim char(1)) returns int
  return (length(replace(str, delim, concat(delim, ' ')))  - length(str)) $$

CREATE PROCEDURE tokenize (str varchar(200), delim char(1))
BEGIN
  DECLARE i INT DEFAULT 0;
  create table tokens(val varchar(50));
  WHILE i <= splitter_count(str, delim) DO
    insert into tokens(val) select(substring_index(SUBSTRING_INDEX(str, delim, i+1), delim, -1));
    SET i = i + 1;
  END WHILE;

END $$

DELIMITER ;

This will tokenize your string, and insert the values into a table called "tokens", one token per row. You should be able to modify it to do something useful pretty easily. Also, you may want to increase the input length from 200.