help with query

[NoBullMan]

Hi,
i am trying to write a query to find whether a number exists in a list of number (separated by comma) in a field.
The field can contain something like 19,20. I want to be able to see if, for example, 19 is in that field.

I tried LIKE but it doesn't seem to work. If the option passed is "1" and the field contains 19, it returns true.

$id = 1;
SELECT name FROM TABLE1 where FIELD1 LIKE '%$id%', returns true if FIELD1 contains 19. '%$id' doesn't work because of ",20"

Anything else aside from LIKE I can use?

[bogu]

SELECT * FROM table WHERE field LIKE '{$id},%' OR  field LIKE '%,{$id}' OR  field LIKE '%,{$id},%'

[tomhath]

 SELECT * FROM table WHERE field LIKE '{$id},%' OR  field LIKE '%,{$id}' OR  field LIKE '%,{$id},%' 

That will also find 119, 190, etc.

What you want is a regular expression: http://dev.mysql.com/doc/refman/5.0/en/regexp.html

[bubblenut]

tomhath wrote:

 SELECT * FROM table WHERE field LIKE '{$id},%' OR  field LIKE '%,{$id}' OR  field LIKE '%,{$id},%' 

That will also find 119, 190, etc.

Are you sure about that?

tomhath wrote:

What you want is a regular expression: http://dev.mysql.com/doc/refman/5.0/en/regexp.html

Agreed, that would be a better sollution, prehapse

SELECT * FROM table WHERE field REGEXP '(^|,)$id(,|$)'

[tomhath]

"Are you sure about that?"

The SQL posted by bogu will work correctly.

Not sure what I was thinking 😕

[bogu]

tomhath wrote:

"Are you sure about that?"

The SQL posted by bogu will work correctly.

Not sure what I was thinking 😕

You were not thinking. 🙂

[tomhath]

"You were not thinking."

I'll blame it on the beer. 😃

[bogu]

tomhath wrote:

I'll blame it on the beer. 😃

CHEARS 😃