Problem searching a field with SQL query LIKE ('%%')

[lilpete]

Hi,
I have a table, with a `Size` field, I would like to search through each entry on this field and display shoes with only the size searched..
An example of the field data would be this:-

'36,37,38,39,40,41,42'

They are shoe sizes.
I use UK and EU sizing on the site so sometimes it looks like this:-

'3,4,5,6,7,8'

The problem I have is when I use this SQL statement to get back search results:- (for example searching for size 3)



SELECT DISTINCT `ID`
FROM `the_table_in_question`
WHERE `Size` LIKE ('%3%')


It returns everything with a 3 in it.. which is what Ive asked of it.

So the question; is there a way through SQL to show results containing a 3, but not display the ones that are 36, 37, 38 and 39

Thanks for your time.
Best Regards,
Pete

[JShor]

Use the NOT operator.



SELECT DISTINCT `ID`
FROM `the_table_in_question`
WHERE `Size` LIKE ('%3%')
AND `Size` NOT IN ('36','37','38','39','40','41','42');

[lilpete]

Thanks for the reply, much appreciated, but thats not going to work for me..

It has to be a dynamic statement, should have mentioned before..

So the size is inserted using my system with tags like this [_in size_].
This will put in the size entered from the search box, so it has to be one SQL statement for all queries...



SELECT DISTINCT `ID`
FROM `the_table_in_question`
WHERE `Size` LIKE ('%[_in size_]%')


So it could read like this:-


SELECT DISTINCT `ID`
FROM `the_table_in_question`
WHERE `Size` LIKE ('%39%')


And your statement would kill that query.

Thanks again for having a go! :D
Best Regs,
Pete

[Brillig]

Your question isn't entirely clear, but it seems like you don't need the like and the %.

If the data from the field is

'36,37,38,39,40,41,42'

And you want to return all those sizes, it's just:


select distinct 'id'
from table
where size in (36,37,38,39,40,41,42)