Searching for a value in a combined MySQL column

May 18th, 2014 by Alex Leave a reply »

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";

2 comments

  1. Morteza says:

    Hello Alex
    I’d like to give a thank to you for an old post that helped me a lot
    http://graphicmaniacs.com/note/getting-a-cross-domain-json-with-jquery-in-internet-explorer-8-and-later/
    Please remove the question mark after diagnostics=true in all yahooapis urls above. It killed lots of my time!
    Btw, I didn’t find out why you limited its title to IE8+ !

  2. Alex says:

    Hey Morteza
    Thanks, indeed it is a bit weird why that happened only in IE8 and IE9 (by the time of writing) and worked well without a hack in IE7 and IE6. But let’s leave that on the Internet Explorer developer’s conscience :)

Leave a Reply