CODEWITHSUNDEEP
mysql
max_
max_

Reputation: 24481

Select all where field contains string separated by comma

I have a field called 'tags' containing a comma delimited list of tags, such as:

ios, ios development, freelance, new_project

I would like to query the field to check to see if the value 'ios' is in the comma delimited list. Please could you tell me how I could do this?

Upvotes: 1

Views: 5148

Answers (3)

LSerni
LSerni

Reputation: 57388

With MySQL, you can use FIND_IN_SET():

SELECT * FROM mytable WHERE FIND_IN_SET('ios ', tags) > 0;

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

Note that FIND_IN_SET expects strings to be comma separated, not comma and space separated. So you might have problems with the last tag. The really best way would be to normalize the table; otherwise you might expunge spaces from the tags column; finally you can work around the problem by adding a space to the tags column:

SELECT * FROM mytable WHERE FIND_IN_SET('ios ', CONCAT(tags,' ')) > 0;

If the number of tags is limited, you might consider converting the column to a SET. This will greatly improve efficiency.

UPDATE

Except that I got the spaces wrong. They are before the strings and not after.

So:

SELECT * FROM mytable WHERE FIND_IN_SET(' ios', CONCAT(' ', tags)) > 0;

But again, get rid of those spaces - they're nothing but trouble :-)

UPDATE 2

The above works, okay. But, that's almost all you can say in my favour. Not only the solution is quite *in*efficient, it also makes the system next to unmaintainable (been there, done that, got the T-Shirt and a chewed-out ass underneath same). So I'll now harp a bit in favour of normalization, i.e., having at least these two more tables:

CREATE TABLE tags ( id      INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
                    tagName varchar(32) // I'm a bit of a cheapskate
);
CREATE TABLE has_tag ( tableid INTEGER, tagid INTEGER );

How much is this better? Let me count the ways.

  • you can easily add more flexible queries ("has all tags in iOS, C++, ..." or "has at least three tags among these: ( iOS, Python, SQL, .NET, Haskell )". Yes, you can do this with FIND_IN_SET, but trust me, you will not enjoy it.
  • you have a controlled dictionary of tags, which empowers you to check whether any tag is known (as well as easily generate lists such as drop-down combo boxes, or -- did anybody say 'jQuery Autocomplete'?).
  • saves some disk real estate (tags are written once)
  • searches are fast. If you already know the tags you're looking for and pre-compile them into tag IDs, they will leave SQL rubber scorch marks on the pavement when running (indexed search for an integer value!). And tags that won't compile will not be there, and you will know this even before the search starts.
  • makes much easier to rename tags
  • can hold any number of tags (you risk having some tag truncated to 'iOS Developm' sooner or later...)

I believe that the "CSV field" is cens(or)ed among the SQL Antipatterns ( http://pragprog.com/book/bksqla/sql-antipatterns ), and for good reason.

Upvotes: 5

TheHe
TheHe

Reputation: 2972

first (simple):

WHERE tags LIKE '%ios,%'

Upvotes: 0

mellamokb
mellamokb

Reputation: 56769

Here is a simple method that works well for comma-delimited lists:

select *
  from mytable
 where concat(', ', tags, ',') like concat(', %ios%,')

Upvotes: 1

Related Questions