Finding a number in a list query

[epaisley]

I have a field (Mtypes) in table TypeData that contains a comma delimitted list of numbers (1,2,3,4,10,13,25). What is the best way to find all the records that have mtypes that contain "1"?

[r937]

the best way?

normalize your table

instead of table TypeData having a single column with multiple values in it, you need (possibly in a separate table) a single column with single values in multiple rows

[epaisley]

Although that would be the best way to do it, I do not control and manage the database, I just get to query it so I'm stuck working with it the way it is.

I guess one option would be to build a temp table on the fly and populate it one row at a time with the values in field and then query the new table, but i think there has to be a way to query the original table and get the values I need

[r937]

okay, i'm sorry to hear about your sad situation, i've been there and in fact i'm the guy who posed for the picture on the t-shirt

try this:

... where concat(',',mtypes,',') like concat('%,',searchterm,',%')

[epaisley]

Well I got something to work, and can now build from there. I'm using ColdFusion so I just built a new query in memory using CF function QUERYNEW, that "normalized" the table and then queried that data set to get the data I need. It's not the quickest method, but speed wasn't an issue in this case.

I also tried your suggestion of using

where concat(',',mtypes,',') like concat('%,',searchterm,',%')

but I'm using SQL Server 2000 and it would throw an error saying
Server: Msg 195, Level 15, State 10, Line 5
'concat' is not a recognized function name.

Thanks for your help, you got me going in the right direction

[r937]

well, duh

i am an idiot ® © ™

i gave you mysql syntax

that's because it's almost always some php programmer in love with the $explode function who stores a comma-delimited list in a column

i should have given you standard sql syntax

sql server would've barfed on that too, though

[Dangermouse]

Off Topic:

$explode function? That would be a variable, i think you mean explode()

[r937]

i'm certain that's what i meant