CODEWITHSUNDEEP
phpmysqlsql-like
medk
medk

Reputation: 9529

LIKE to select only the exact keyword

Is it possible to SELECT LIKE the exact word in comma-separated column?

tags: php,web,webdevelopment,java,javascript

'SELECT * FROM table WHERE tags LIKE "%java%"'

This will select "java" and "javascript"

Is it possible to select ONLY "java"?

Thanks.

Upvotes: 0

Views: 63

Answers (5)

juergen d
juergen d

Reputation: 204756

You can use FIND_IN_SET for that

select * from your_table
where find_in_set('java', tags) > 0

But actually you should rather change your table definition. Never store multiple values in a single column.

A better DB design would be

posts table
-----------
id
title
body
...


tags table
----------
id
name
...


post_tags table
---------------
post_id
tag_id

Then you could query posts having the java tag like this

select p.*
from posts p
join post_tags pt on pt.post_id = p.id
join tags t on pt.tag_id = t.id
where t.name = 'java'

That is much cleaner, easier and also very fast.

Upvotes: 2

Mnemonics
Mnemonics

Reputation: 699

1NF: The domain of each attribute contains only atomic values. Redesign your table to support 1NF. EDIT: What do you think happens when you want to update the column? Or you want to remove java from the table? It is much easier to access, delete and update rows when they follow normalization rules.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

First, you should store tags in a junction table, with one row per whatever and per tag. This is the right way to store lists in SQL.

Another answer shows how to use find_in_set(). If you are keen on using like, you can do:

WHERE CONCAT(',', tags, ',') LIKE "%,java,%"'

Although the performance should be similar to find_in_set() in MySQL, the only advantage to this method is that it will work in more databases. Let me repeat, though, that the right thing to do is fix the data structure to have a junction table rather than storing lists in a string column.

Upvotes: 1

Marek
Marek

Reputation: 7433

MySql supports regular expressions, regular expressions support word boundaries:

SELECT * FROM table WHERE tags REGEXP '[[:<:]]java[[:>:]]';

Upvotes: 1

Sourabh Kumar Sharma
Sourabh Kumar Sharma

Reputation: 2807

Another answer given before me is good but i had another solution that i felt to share.

SELECT * FROM table WHERE tags  LIKE "java" OR tags   LIKE "%,java," OR tags   LIKE "java,%"

Which simply means that there can be 4 conditions in the column:
tags column has only java in it
tags column may have ,java, in it
tags column has only java, in it

Useful link: http://www.w3schools.com/sql/sql_like.asp

I hope this helps you.

Upvotes: 0

Related Questions