Problem with comma-delineated field

[mrDrive]

Hi,

I have a field in myTable ("States") that is a comma-delineated integer list.

RecordID States
------------------------------
1 1
2 1,2
3 12

I'm trying to write a query that will return rows where a specific integer value is present.

When I use the LIKE clause...

SELECT *
FROM myTable
WHERE States LIKE '%1%'

it returns:

RecordID States
------------------------------
1 1
2 1,2
3 12

If I use the IN clause...

SELECT *
FROM myTable
WHERE (States IN ('1'))

it returns:

RecordID States
------------------------------
1 1

instead of what I'm looking for which is:

RecordID States
------------------------------
1 1
2 1,2

What method should I be using here?

Thanks in advance for any help!!!

mD

 

[jbenson001]

Try:

Select * from MyTable
Where CharIndex('1', States) > 0


However this will cause a problem if you have integers like 10, 11 etc. you will get those rows as well. You should changed your table structure to have one row with an ID and one state like:

ID State
1 1
1 2
2 2
2 4


etc...

 

[JayKusch]

SELECT *
FROM myTable
WHERE States LIKE '1'
OR States LIKE '1,'


Thanks

J. Kusch

 

[mrDrive]

Kool!

Nice solution JK, "unorthodox" maybe, but so is making me build an app with a poorly designed 2000+ record table. I'd love to restructure the table, but I try keep the interns on my good side. LOL

Thanks for the help!

 

[mercwrought]

SELECT *
FROM #t
WHERE states like '%1%' 
and states not like '%[0-9]1%' 
and states not like '%1[0-9]%'

try this

 

[mrDrive]

Wow, that's a really clever solution! That looks like the winner.

Thanks a bunch!

 

[mercwrought]

well there is a flaw in that one i'll let you figure it out but here is one that realy works you just have to go through all of the poasblities

SELECT *
FROM #t
WHERE states like '%1%'
AND (states like '%,1'
or states like '1,%'
or states like '1')

 

[JamesLean]

Another option for you to try:

WHERE ',' + states + ',' LIKE '%,1,%'

--James