«

»

Dec 26

Explode string in MySQL

If you know your PHP there is this nifty function called explode which allows you to turn an string into an array by splitting it. quite sure Javascript has a similar function called split which you call from a string like an object.

Now if you wanted to do the same thing in MySQL you’d be out of luck, mainly because people will say there are no arrays in MySQL. thing is these people are wrong.

Well technically they are correct however if you think about it what exactly is an array? take the following.

<?php
	
	$myArray = array(	array("firstname" => "cave", "lastname" => "jonson"),
						array("firstname" => "john", "lastname" => "smith"),
						array("firstname" => "bob", "lastname" => "eliot"));
	
?>

what does that remind you of? yep a table. sure you access it differently but you have 3 rows populating 3 fields and each with a unique id in the from of an array Index.

So keeping this in mind we can actually create Explode Functionality in MySQL

DELIMITER $$
/*
Name:			Explode
Input:			originalString	<Argument 1>	[TEXT]
				delim			<Argument 2>	[VARCHAR]
Output:         N/A
Description:
Generates a sequence of numbers that get stored in a table.
*/
CREATE PROCEDURE Explode(IN originalString TEXT, delim VARCHAR(255))
BEGIN
	SET @total = (LENGTH(originalString) - LENGTH(REPLACE(originalString, delim, ''))) / LENGTH(delim)+1;
	DROP TABLE IF EXISTS explodeArray;
	CREATE TABLE `explodeArray` (
		`Index` int(11) NOT NULL auto_increment,
		`string` VARCHAR(255) NULL,
		PRIMARY KEY  (`Index`)
	);
	
	CALL GenSequence(@total, 1, "explodeArray", "Index");
	
	WHILE @total > 0 DO
		UPDATE explodeArray
		SET `string` = REPLACE(SUBSTRING(SUBSTRING_INDEX(originalString, delim, @total),
			LENGTH(SUBSTRING_INDEX(originalString, delim, @total-1)) + 1),
			@total, '')
		WHERE `Index` = @total;
		SET @total = @total - 1;
	END WHILE;
END$$

DELIMITER ;

SET @string = "Test Na Hello";
SET @delim = " ";
CALL Explode(@string, @delim);
SELECT * FROM explodeArray;

Now before you use this code be sure you get my MySQL Number Sequence Generator. you can do without it so long as you do something about line 20.

First we want to work out how many instances of our delimiter there is so with the code

SET @total = (LENGTH(originalString) - LENGTH(REPLACE(originalString, delim, ''))) / LENGTH(delim)+1;
  1. we get the length of our string (in this case 13)
  2. we get the length of our string after we have taken out our delimiter (in this case its 11) and subtracted it from the lnegth of our original string, the result being 2
  3. we now divide this by the length of our delimiter’s length to get how many times it appeared

Now we create a table which will be used to emulate an array and auto-generate the indexes. once that is completed we than start updating this table bu getting the substrings from our original string

  • SUBSTRING_INDEX returns a subtring from the left of the instence of the delimiter we specified
  • using REPLACE we want to get the string left from where our current instance of the delimiter is and replace all the character left of the previous nstance of the delimiter with nothing
  • we also include our delimiter for all our substrings after the first. this can be removed later on however you want. i personally perfer to keep it

now when you do the SELECT you’ll see our explodeArray table. sure our emulated array doesn’t start at 0 but if that really is a problem for you you can alter the code to your liking.

Now i can’t guarantee the performance on this with very, very, very long strings which would create 200 size tables but if you are doing something like that you might want to have a look at your data and ask yourself why you are exploding a string that large into so many parts

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>