mySQL Like statement not working as output needed

Question

I have item_ID column which has this number inside (1,2,3,4,5,6,7,8,9).I am running this query now

SELECT * FROM `coupons` WHERE item_ID like '%17%'

Still it's finding row. It should not find any row if item_id is parsed 17. Is there any problem with my query?

Update

Then dont use LIKE. If you know the key and you want just that row then use `WHERE itemid = '17'` — RiggsFolly, Dec 20 '16 at 10:23
Use LIKE to find sub strings within a larger string. Not to select specific records — RiggsFolly, Dec 20 '16 at 10:23
@RiggsFolly item_id column has not single specific number so I can't use itemid = '17' — SUN, Dec 20 '16 at 10:24
Oh you mean `item_ID` contains a comma seperated list of ID's. ___Bing-Bong___ **Database design error** — RiggsFolly, Dec 20 '16 at 10:28
@SUN please post some rows out of your coupons table to you question. — helle, Dec 20 '16 at 10:29
@helle **Waaaay** There is one released into the wild every minute!!! :) — RiggsFolly, Dec 20 '16 at 10:30
Assuming you have a nicely formed item_ID you could try `LIKE '17,'` but this would assume you add a trailing comma on the last id in the list. Really you should throw away this database and do a quick Database Design for beginners tutorial — RiggsFolly, Dec 20 '16 at 10:34
@Barmar not sure this is a duplicate OP is asking why a coupon is found WHERE item_ID like '%17% when it does not exist in (1,2,3,4,5,6,7,8,9)? — P.Salmon, Dec 20 '16 at 11:34
@P.Salmon His screenshot has `...` at the end. I suspect the field contains something like `,117` or `,175` further on. If he uses `FIND_IN_SET` it won't get incorrect matches like that. — Barmar, Dec 20 '16 at 11:39

helle · Answer 1 · 2016-12-20T10:57:32.763

0

I assume your item_ID is some numeric type, right? So you can use the = operator:

If it realy is varchar (which I do not recommand for an identifier variable, because of performance issues), you would use:

SELECT * FROM coupons WHERE item_ID = '17';

If you want to find IDs containing the substring 17 then your query is allright.

If you have a commaseperated list of your IDs as string in that field (which I do not recommand) you should try this:

SELECT * FROM coupons WHERE item_ID LIKE '%,17,%' OR item_ID LIKE '%,17' OR item_ID LIKE '17,%' ;

check your content of that itme_ID field. 117, 1710, ` etc. can be found with your query as well.

edited Dec 20 '16 at 10:57

answered Dec 20 '16 at 10:25

helle

No Item_ID is not single item ID . It is collection of many ID so this can not be used – SUN Dec 20 '16 at 10:27
sheeeesh, please update you question and give us more details. thanks – helle Dec 20 '16 at 10:31

1 Answers1