1 min.
First, the code #
Copypaste the following query and execute it as a MySQL query:
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 #
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'.