I am having records in a field (field_id) of a table (in mysql database), which is having ID stored per line, for example


above is one record, and I have many records similar to that.

and, when I am writing the query SELECT * FROM $table_name where field_id like '%1%'

then the rows having the results like

are also returned. I am upset. How to overcome this problem?

I found one fix to this, but it fails in most of the conditions, this is -

SELECT * FROM `$table_name` WHERE field_id like '%\n1%' OR '%1\n%'

This fails, when the (field_id) have only one record, for example, '1'. I want to catch everything, where line equals to 1, whether single line in the record, or many.

I am going mad for this.

Can someone provide me any way? Thanks.

It's quite unclear what you want. Do you want all records with a field_id=1? Then just select them. Or do you want all records in which a text field contains the string representation of the number 1? In this case use a regular expression:

select * from mytable wher field_id rlike '(^|[^0-9])1($|[^0-9])'

Also it looks like a flawed database design to have several id entries in one field.

Wow. Thanks smantscheff Sir, thats really great. It worked. Thanks a lot.