Search a string for a specific value

[Kaplanconstruct]

I've tried a hundred things and have not got this to work. A million thanks to anyone who can help!

I have a string that consists of numbers such as 3,4,8,9,10

So I want to setup a select query to pull a value from the database if that value is located inside the string

FYI -The string is stored in a session variable....in coldfusion of course

Here is what mine looks like


<cfquery datasource="blah" >
SELECT sitenum
FROM websites
WHERE #session.rank.keep# LIKE %#webup.sitenum#%
</cfquery>

so the where in data from would be: Where 3,4,5,2,9 LIKE %4% Thanks!

[cf_dev2]

> LIKE %#webup.sitenum#%
Edit - I'm assuming that's a typo and that "siteNum" is a column in your table and not a variable

If the "siteNum" column type is numeric, don't use LIKE. Try an IN (...) clause instead

--- should use cfqueryparam here
WHERE sitenum IN ( #session.rank.keep#)

Which would translate to

WHERE sitenum IN (3,4,8,9,10)

[Kaplanconstruct]

Bingo, thanks

[Dan_Bracuk]

Not quite. If this list of numbers is coming from a series of check boxes or something, add something to deal with the possibility that none of them get checked.

[cf_dev2]

Yes, if you're not doing it already you need to add validation to handle an empty or non-existent list of values. Without it your query will throw an error

-- causes error
WHERE sitenum IN ( )

... and consider using cfqueryparam with the "list" attribute