This time we will have to search for a value inside a comma-delimited column. Let’s say we have this structure:
name | location ---------------- Alex | Philadelphia, United States Bob | Athens, Greece John | London, England Bill | Kiev, Ukraine Mark | Warsaw, Poland Harry | Odessa, Ukraine Jane | Chicago, United States
First, let’s make sure the SPLIT_STR function exists in our database:
CREATE FUNCTION SPLIT_STR( x VARCHAR(255), delim VARCHAR(12), pos INT ) RETURNS VARCHAR(255) RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, '');
Now we need to get all people from Ukraine, what we can do is create a new temporary table, split the location in two columns and then we can perform a search.
CREATE TEMPORARY TABLE `users_new` SELECT `name`, TRIM(SPLIT_STR(`location`, ',', 1)) AS `location1`, TRIM(SPLIT_STR(`location`, ',', 2)) AS `location2` FROM `users`;
We might want to additionally add an index while we are doing our magic:
ALTER TABLE `users_new` ADD INDEX ( `location2` )
And now we can simply SELECT from our new temporary table. Note, that all the queries need to be run in one connection, because temporary table will go away as soon as we disconnect (or our script disconnects).
SELECT * FROM `users_new` WHERE `location2` = "Ukraine";