search a list

[twcman]

I have a field in my db rprods that was inserted from a form with a select statement with multiple enabled. the list could look like 1, 2, 3, 4 ...

I need to run a query to find all the records that have a 2 in the list. Not 22 or 12 but just 2.

What would the query look like...

Select *
From tbl
Where ?

Thanks....
The only dumb questions are the ones that are never asked

 

[GunJack]

Hey twc,

The rows you want to select can fall into one of 4 situations.
1. The field only contains a 2
2. The field starts with a 2 and contains other values.
3. The field contains a 2 mixed in between other values.
4. The field contains other values but ends with a 2.

The following select should catch all 4 possibilities.

select * from table1
where myField like '2'
or myField like '2,%'
or myField like '%,2,%'
or myField like '%,2'

If you want to select on multiple values, you can just copy the block and "or" them together like this:

select * from table1
where

( myField like '2'
or myField like '2,%'
or myField like '%,2,%'
or myField like '%,2 ')

or

( myField like '3'
or myField like '3,%'
or myField like '%,3,%'
or myField like '%,3 ')

Hope this helps,
GJ