Query finding data IN

Have a query here I am not sure how to find the answer.... Have a field in the database with a comma list of items...and want it to only pull a record based upon a session variable that is a single integer. So how do I say give me records where database field(comma delimited) contains session variable? example: Where table.sites HAS session.variable ....I know has isn't the right term, but this is the english of it. Thanks. -------------------------------------------------------- Eric J. Hoffman Managing Partner 2081 Industrial Blvd StillwaterMN55082 mail: ehoffman@ejhassociates.com www: www.ejhassociates.com tel: 651.207.1526 fax: 651.207.1536 mob: 651.245.2717 -------------------------------------------------------- This message contains confidential information and is intended only for cf-talk@houseoffusion.com. If you are not cf-talk@houseoffusion.com you should not disseminate, distribute or copy this e-mail. Please notify ehoffman@ejhassociates.com immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. Eric J. Hoffman therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. --------------------------------------------------------

If you don't want to change the DB design you could try: SELECT * FROM YourTable WHERE YourColumn LIKE '%,#YourVariable#,%' OR YourColumn LIKE '#YourVariable#,%' OR YourColumn LIKE '%,#YourVariable#' Given a values like: 1,2,3,7,4,5,6 7,1,2,3,4,5,6 1,2,3,4,5,6,7 Looking for 7 it should match all three. Which seems like overkill, but looks like it'd work. Now I'm sure there might be a better way to do this. Personally I'd change the table design. Adrian Have a query here I am not sure how to find the answer.... Have a field in the database with a comma list of items...and want it to only pull a record based upon a session variable that is a single integer. So how do I say give me records where database field(comma delimited) contains session variable? example: Where table.sites HAS session.variable ....I know has isn't the right term, but this is the english of it. Thanks. -------------------------------------------------------- Eric J. Hoffman Managing Partner 2081 Industrial Blvd StillwaterMN55082 mail: ehoffman@ejhassociates.com www: www.ejhassociates.com tel: 651.207.1526 fax: 651.207.1536 mob: 651.245.2717

Since the are only 3 possible values and one session variable, it shouldn't be that big of a deal?  Seemed like a lot more overhead setting up pivot tables or something similar... Regards, Eric J. Hoffman Managing Partner EJH & Associates, LLC A Decision That Matters. EJHA. -------------------------------------------------------- Eric J. Hoffman Managing Partner 2081 Industrial Blvd StillwaterMN55082 mail: ehoffman@ejhassociates.com www: www.ejhassociates.com tel: 651.207.1526 fax: 651.207.1536 mob: 651.245.2717 -------------------------------------------------------- This message contains confidential information and is intended only for cf-talk@houseoffusion.com. If you are not cf-talk@houseoffusion.com you should not disseminate, distribute or copy this e-mail. Please notify ehoffman@ejhassociates.com immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. Eric J. Hoffman therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. -------------------------------------------------------- ________________________________ Sent: Mon 2/6/2006 8:34 PM To: CF-Talk Subject: RE: Query finding data IN If you don't want to change the DB design you could try: SELECT * FROM YourTable WHERE YourColumn LIKE '%,#YourVariable#,%' OR YourColumn LIKE '#YourVariable#,%' OR YourColumn LIKE '%,#YourVariable#' Given a values like: 1,2,3,7,4,5,6 7,1,2,3,4,5,6 1,2,3,4,5,6,7 Looking for 7 it should match all three. Which seems like overkill, but looks like it'd work. Now I'm sure there might be a better way to do this. Personally I'd change the table design. Adrian Have a query here I am not sure how to find the answer.... Have a field in the database with a comma list of items...and want it to only pull a record based upon a session variable that is a single integer. So how do I say give me records where database field(comma delimited) contains session variable? example: Where table.sites HAS session.variable ....I know has isn't the right term, but this is the english of it. Thanks. -------------------------------------------------------- Eric J. Hoffman Managing Partner 2081 Industrial Blvd StillwaterMN55082 mail: ehoffman@ejhassociates.com www: www.ejhassociates.com tel: 651.207.1526 fax: 651.207.1536 mob: 651.245.2717

In general, you don't want to store multiple values in a single cell...a relational database just isn't designed to handle data that way...there are string functions like charindex that can search through those fields, but the database engine is not optimized for that...a query that has to use one of those functions is going to take longer to return because it has to touch every record in the table. It might work all right with just a few records in the table, but it is going to start slowing down as more data is put in there. > Since the are only 3 possible values and one session variable, it shouldn't be that big of a deal?  Seemed like a lot more overhead setting up pivot tables or something similar...

> Where table.sites HAS session.variable ....I know > has isn't the right term, but this is the english of it. I believe with SQL Server that you can use a comma-delimited field as the target of an IN function as below.  It's been a while since I've used code like this, so I could be remembering wrong. WHERE #session.variable# IN (table.column) -Justin Scott

The IN operator is used the other way around, but won't help in this scenario...if the possible values really are 1-9, then you could use charindex...otherwise you might look at using patindex. ----- Excess quoted text cut - see Original Post for more -----

Just to be certain, I ran some tests and confirmed that my remembrance was indeed incorrect.  I was probably getting the direct use of a column mixed up with using a sub-query to pull values.  I've been programming with abstract interfaces that shelter me from having to write my own queries too long and it's making my SQL knife dull. -Justin Scott ----- Excess quoted text cut - see Original Post for more -----