Simple LIKE query not working


I have a field in database in which i have stored comma seperated keywords like: abc, def, ghi, jkl

Then I am doing a query like: “SELECT * FROM tablename WHERE keywords_field LIKE %def%”;

I am doing a simple query like that but its not returning any result. How to do ? I have used * also instead of % but that is also giving any results. Is it due to the comma seperated keywords in database ?

How to fix ?


Just checking the obvious are you putting %def% in quotes. Without them it is an invalid sql syntax.

SELECT * FROM tablename WHERE keywords_field LIKE '%def%'

i urge you to redesign it :slight_smile:

that’s correct, it isn’t returning any result because it’s returning an error message instead



Why redesign it ? And yes you are correct it was returning an error.


plays swami with Rudy

Because if multiple entries have the same keywords, it’s less redundant to store them in a separate table and join the two by id’s…

because it violates first normal form

fix it, and your queries will be (1) simpler, and (2) faster

are those reasons sufficient?

sorry, swami be wrong :slight_smile:

i would use the actual keywords, ~not~ ids

besides, the “redundancy” is not eliminated by using ids, that’s a huge myth – there would be just as many redundant id values as keyword values

We’ve had this conversation before - unless the keywords were shorter than 3 characters, id’s are faster :stuck_out_tongue:

comma seperated values do not necessarily violate 1NF, but should still be avoided. My google-fu is strong.


Makes sense.


yes, and you lost the argument the last time, too

using ids requires a join

i don’t care how fast your ids are when doing joins, nothing beats not doing the join at all


yes, they do, and yes they should


So many-to-many… you’re suggesting he put… what? You dont want him to do a join, you dont want him to put CSV… what was the actual suggestion? I’m lost.

“i’ll explain it so even you can understand it” – moe howard

:slight_smile: (nothing personal intended, i just thought it was an apt quote)

PK ( prodcode )

prodcode FK
keyword [FK]*
PK ( prodcode , keyword )

will that suffice or would you like to see sample data? :slight_smile:

this design ~is~ actually many-to-many, by the way

  • the keywords table may or may not exist, depending on whether you want a controlled vocabulary or freeform

Oh okay, so it IS joining. You’re just saying avoid the join table. gotcha.

productkeywords is the join table, the table between products and keywords (if the keywords table exists)

it’s a separate table in order to avoid the comma-separated list in the products table

naturally, you ~do~ have to join back to the products table to find the product data

but you ~don’t~ have to join to the keywords table, as would be required by the keyword ids you were suggesting in post #5

If the Keywords table exists (which would be the case if you wanted to limit keyword choices… otherwise the keywords table is redundant and irrelevant), you have to join to the keywords table…

How do you not join to it?

so far, so good :slight_smile:

you don’t join to it by not joining to it

i’m not sure what you aren’t getting

list products along with their keywords for selected products –

SELECT p.prodcode
     , p.descr
     , p.price
     , GROUP_CONCAT(pk.keywords) AS keywords
  FROM product AS p
  JOIN productkeywords AS pk
    ON pk.prodcode = p.prodcode
 WHERE p.descr LIKE '%pants%'
    BY p.prodcode

find products which have a certain keyword –

SELECT p.prodcode
     , p.descr
     , p.price
  FROM productkeywords AS pk
  JOIN product AS p 
    ON p.prodcode = pk.prodcode
 WHERE pk.keyword = 'sandal'

see? no keywords table is required in the queries, even though it exists for referential integrity of keywords

methinks you’ve been using ids too long :wink:

an interesting point… though you’ve inflated your database by storing “sandal” over and over and over instead of 1 over and over…

so? your point is?

let me ask you this, when you create a persons table, do you extract all the first names and store them in a separate table, replacing them in the persons table with an id, just to avoid storing “john” and “mary” over and over instead of 42 and 63 over and over?

didn’t think so :smiley: