Get a unique id from a comma separated string

[mapleleaf]

I have a field in the database with ids in it.

Eg 3 fields below:

1,43,89,4,923

55,14, 402,77

44,8,23

 

How do I search for only the id 4 and not return 44, 43, 14, 402 etc?

 

In this case i want only the first row returned.

 

I am sure MYSQL will have someting for this.

 

Thanks

[JonnoTheDev]

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

[fenway]

FIND_IN_SET() will work too under limited circumstances.

[mapleleaf]

I am not sure whether regex will work for this as I need the query to be able to find any id 2 or 22 or 32 etc.

 

For FIND IN SET I am struggling to write a query that might work. Seems like it needs subqueries which I am not familiar with.

 

i am trying something like this:

 

SELECT IN SET( '6', '( SELECT second_theme FROM articles WHERE second_theme LIKE ' %6 % ')' )
FROM articles

 

Any ideas much appreciated

[rajivgonsalves]

try this

 

SELECT FIELD_IN_SET( '6', second_theme )
FROM articles

[mapleleaf]

SELECT *
FROM articles
WHERE second_theme
IN ( 6 ) ;

 

is what I was looking for.

 

Thanks for the thoughts. I suspect my question wasn't phrased right