Comma delimited list query find 1

[stilllwaiting]

Hi there.

I am building an app. using ColdFusion along with Mysql.
I need to create a select query that checks if certain Element ID's are contained in a comma delimited list which are stored in the MySQL database.

Normally, this would be easy, just something along the lines of:

select * from table_name
where Comma_list like '%ID1%'

The problem comes in the creating of the ID's there's nothing to stop the user from creating an ID that contains the entirty of another id...example:

The following ID's can exist: ID1, ID12, ID101, ID41

So, if I'm trying to select a row with a list that contains 'ID1' using the query example above, and the computer see's the following field

COMMA_LIST = ID7,ID42,ID15

and return the row mistaking ID15 for ID1

There is a LISTFIND function in ColdFusion, but it is murder on the processing time to have to check the list afterwards.

I'm hoping there's a simple solution I'm just not thinking of. Sadly, I'm not able to alter the process of creating the ID's nor of their storage in comma delimited lists.

Thanks in advance for any input.

 

[stilllwaiting]

Afterthought:

I'd thought of searching, instead, for

where Comma_list like '%ID1,%'

However, if it's the last element in a list, it won't pick it out. Ideally, I'd just make it so the list ended with a comma from the beginning, but I don't have control over that side of the process...and am forced to a way to work around it.

 

[TonyGroves]

How about:
[tt]
SELECT *
FROM
table_name
WHERE CONCAT(Comma_list,',') LIKE '%ID1,%'
[/tt]
assuming there is no whitespace before the commas or at the end of the list.

To cater for whitespace, you could use:
[tt]
SELECT *
FROM
table_name
WHERE REPLACE(CONCAT(Comma_list,','),' ','') LIKE '%ID1,%'
[/tt]


-----
ALTER world DROP injustice, ADD peace;

 

[stilllwaiting]

Wow...worked like a charm.

Thanks for the super-fast reply.