(Update )

MySQL split string

I regularly crawl websites, and sometimes they have a 'meta keywords' field that I want to check. Use this MySQL function to split a string inside a MySQL field. For example, to split a list of comma separated keywords into individual keywords.

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) ,
		CHAR_LENGTH(
			SUBSTRING_INDEX(str , delim , pos - 1)
		) + 1
	) ,
	delim ,
	''
);

How to use

I will show you 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 '[space][space][space][space]keyword 4', where [space] means an extra non-breaking space ;) The trim() wrapper takes care of those excess [space]s and simply returns 'keyword 4'.

What's next?

Not much really. But if you think this was useful to you, it might be for others also: