0

I have table column which holds CSV values. eg: 2,3,4,1,13 I want to find specific string from the CSV and return the row matching exact search string from csv.

Table:

tbl_A(ID,name,Csv)

ID name   CSV
1  ABC    1,2,13,15
2  PQR    1,4,3,5

@SearchSting=13

Output required:

ID name  CSV
1   ABC  1,2,13,15


select * from tbl_A  where csv like '%@SearchSting%'

This will give both rows. :(

Help appreciated!

SHEKHAR SHETE
  • 5,964
  • 15
  • 85
  • 143

1 Answers1

2

Left me start by saying that this is a poor database design and should be avoided. If possible, you should normalize your database and change that Csv column to a table containing each value in it's own row. Further reading: Is storing a delimited list in a database column really that bad?

However, if normalizing your database is not an option, you can do this:

SELECT Id, Name, Csv
FROM tbl_A
WHERE ',' + Csv + ',' LIKE '%,' + @SearchString  + ',%'
Community
  • 1
  • 1
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • hi @Zohar Peled, this solution doesnt work in sqlserver! Previous solution worked...please 'Undo' the answer to previous one...! – SHEKHAR SHETE May 10 '16 at 06:13
  • 1
    Done. Please let this be a reminder to always use all the relevant tags in your questions. Other databases uses sql standard || for concatenating strings as a_horse_with_no_name rightfully wrote. – Zohar Peled May 10 '16 at 06:21
  • Is there a reason why you didn't mark the answer as accepted, after you said it works for you? – Zohar Peled May 10 '16 at 13:34