How to Find String with comm separated value in table column

Question

Id  vDivisionNo
1    1,9
2    9
3    1
4    9,1

i have using char index but not find value

select * from DivisionMst where CHARINDEX(',' + vLocationNo + ',', ','+ '9,14' + ',') > 0

And Try this

select * from DivisionMst where CHARINDEX(',' + '9,14' + ',', ','+ vLocationNo + ',') > 0

But Not working please help me.

Normalize your table structure. **Never store data in this way**. — Gurwinder Singh, Feb 10 '17 at 05:28

score 0 · Answer 1 · answered Feb 10 '17 at 05:48

0

use this below query to find the answer

SELECT        *
FROM          DivisionMst
WHERE        (vDivisionNo LIKE '%,%')

answered Feb 10 '17 at 05:48

Wintergreen

score 0 · Answer 2 · edited May 23 '17 at 12:17

You should, if possible, normalize your database.
Read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutly yes!.

If, however, you can't change the database structure, you can use LIKE:

SELECT * 
FROM DivisionMst 
WHERE ',' + vLocationNo + ',' LIKE '%,'+ '9,14' + ',%'

btw, your sample data does not contain a column name vLocationNo, nor does it contain a value '9,14'.

2 Answers2