Looking for a value in a list stored in a database tablefield

[smnbin]

Hello,

I have a field in a database table that contains a comma separated list on
numbers. In my CFQUERY I am attempting to extract records from the database
table where a parsed number is present in the comma separated list. I have
tried the code below, but it is throwing an error.

variables.publicationID is the number that I am looking for in the database
table field.
publicationList is the name of the database table field containing the comma
separated list of numbers.

where <cfqueryparam value="#variables.publicationID#"
cfsqltype="cf_sql_integer"> in publicationList

Can anybody help?

Many thanks,

smnbin

[Kronin555]

The only way to do it with the data model you currently have is as follows:

WHERE publicationList LIKE '#variables.publicationID#' OR
publicationList LIKE '#variables.publicationID#,%' OR
publicationList LIKE '%,#variables.publicationID#,%' OR
publicationList LIKE '%,#variables.publicationID#'

You need to check for 4 cases:
it's the only ID in the list
it's the first ID in the list
it's in the middle of the list
it's the last ID in the list.

The better solution would be to replace this comma-delimited list with a
mapping table, where each row in this mapping table contains a reference to the
main table as well as a single publicationID.

[MikerRoo]

Actually it could be worse than Kronin555 suggests -- if spaces are allowed
around the commas (I've seen it more than once), then you would need up to
<b>nine</b> conditions in your where clause.

If you are using MySQL, you can try the FIND_IN_SET() function but I don't
recommend it.

As Kronin555 hinted, you would be wise to abandon this approach and normalize
this database.

[paross1]

A "<i>database table field containing the comma separated list of numbers.</i>"
is an almost surefire guarantee of a bad data model. Sometimes you get stuck
with such a bad design, but I would do everything I could to try and fix the
model, if it is within your power.

Phil

[smnbin]

Thank you all for your responses.

Kronin555 - I used your solution in the end which appears to work okay as the
list doesn't contain any spaces.

paross1 - I understand your point about the bad data model and if I had a bit
more time to fix the problem properly I would look at making some changes to
the database structure.

Thanks again.

smnbin