How to split a string in MySQL? Use this function.

Databases are not really meant for stuff like this, but sometimes you just need to do something quickly. I use this function a lot in my work. I regularly crawl websites, and sometimes they have a 'meta keywords' field that I want to check. This function helps to quickly extract those keywords, and separate them from each other:

CREATE FUNCTION `SPLIT_STRING`(str VARCHAR(255), delim VARCHAR(12), pos INT) RETURNS varchar(255) CHARSET utf8
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
        LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
        delim, '');

How to use

I will show you an example of how to use this function. Let's create some sample data first:

CREATE TABLE `test` ( `keywords` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`keywords`) VALUES ('keyword 1,keyword 2, keyword 3,     keyword 4');

Next, use the function to return individual keywords from the string, separated by a comma:

SELECT
	-- keyword 1
	SPLIT_STRING(`keywords`,',',1) AS keyword_1,
	-- keyword 4, NOT trimmed
	SPLIT_STRING(`keywords`,',',4) AS keyword_4,
	-- keyword 4, trimmed
	trim(SPLIT_STRING(`keywords`,',',4)) AS keyword_4_trimmed
FROM `test`;

Note that I also used a trim() function around 'keyword_4', which I inserted into the table as '     keyword 4'. The trim() wrapper takes care of those excess spaces and simply returns 'keyword 4'.

Need help?

If you need more help, take a look at the related service(s): Search analytics, Technical SEO.