Finding numbers in recordset.

[Jellyhead]

Easy one for the experienced guys:
I have a record with a list of comma separated values, IE,

1,2,4,6,12,24

or simply

24

I need to write a query where I find all records where the specific number/value I'm looking for is in the list. For example I’m looking for all records that contains the number "2".
I've used the like operator

select * from my_table where my_column like '%#form.my_search#%'

(and a few variants), but the problem is, it finds the record with just "24" in it as well, whereas I want records that contains "2". And not anything that has a 2 in front or behind it.
All numbers are integers, as they refer to a row identifier on another table.
How to do this?

Thanks

[BKBK]

Assuming my_column is of integer datatype,

[Jellyhead]

Thanks, but I need it the other way round.

I'm searching for "2" in a set of "1,2,4,6,12,24", not to see if my recorset contains anyone of "1,2,4,6,12,24"... if you get my drift. the IN function's reverse is what i'm looking for.
The datatype is just text, as each record contains a list of up to 100 integers, each separated by a comma. So I need to run through my table, and pick up every record where the record contains "2".
Your example only returns true if "2" is the only number in my record.

[Dan_Bracuk]

Normalize your database so you don't have problems like this.

[paross1]

To expand a little on Dan's post, whenever you have a column in a database that contains a list of comma separated values, it is an example of a denormalized data model and almost ALWAYS an indicator of a very bad design. If you have any control over the data model and database design, you should migrate column to its own entity and create a proper relationship (one to many, etc.) between the two entities (tables).

Phil

[BKBK]

I'm searching for "2" in a set of "1,2,4,6,12,24", not to see if my recorset contains anyone of "1,2,4,6,12,24"... if you get my drift. the IN function's reverse is what i'm looking for.
The datatype is just text, as each record contains a list of up to 100 integers, each separated by a comma. So I need to run through my table, and pick up every record where the record contains "2".

Okay, I get you. Then what about

<cfquery name="q" datasource="#application.dsn#">
select * from my_table
where my_column like '%,2,%'
</cfquery>
<cfdump var="#q#">

[drforbin1970]

Though I agree with comments here about normalizing databases, if you are stuck with this, here is a solution. A query of a query. Converts your 1,2,3,4,5,6 to '1','2','3','4','5','6' which can then be used with the SQL operator IN.

<!--- Make sure your search parameter is surrounded by single quotes, then surrounded by double quotes --->
<cfset Form.search = "'2'">

<cfquery name="q1" datasource="yourDatasource">
select * , '''' + REPLACE(csv_data,',',''',''') + '''' as mod_data,
'#Form.search#' as search_parm
from test
</cfquery>

<cfquery dbtype="query" name="q2">
select * from q1
where search_parm IN (mod_data)
</cfquery>

<cfoutput query="q2">
#anyColumnYouSelectedInQuery1or2#<br>
</cfoutput>