0

Problem statement ::

I have one database table named as category which contain column name as categoryIds, below is the sample data into categoryIDs column

categoryIds = '17,34,400,12'

Now I have written the SQL query below to find out category

declare @search as varchar

set @search='40'

select * from category where categoryIds like '%' + @search + '%'

above query return result which is wrong as categoryIDs does not contain 40 category

Cœur
  • 37,241
  • 25
  • 195
  • 267
Amol Gunjal
  • 11
  • 1
  • 4
  • 2
    [Normalize](http://en.wikipedia.org/wiki/Database_normalization) your table and create another table which references this via foreign-key. Then you have one record for every value and the query is very simple and efficient (not to mention [sql injection](http://en.wikipedia.org/wiki/SQL_injection) vulnerability). – Tim Schmelter Jan 06 '15 at 11:55

1 Answers1

0

This may help you :

DECLARE @categoryIds VARCHAR(100) = '17,34,400,12'

SELECT t1.nod.value('.', 'varchar(50)') tags
FROM   (SELECT Cast('<N>' + Replace(@categoryIds, ',', '</N><N>')
                    + '</N>' AS XML) AS format) t
       CROSS APPLY format.nodes('/N') AS t1(nod)
WHERE  t1.nod.value('.', 'varchar(50)') = '40'
Deep
  • 3,162
  • 1
  • 12
  • 21
  • Thanks for reply, output of above query is 400 which is wrong it should be NULL because 40 is not present in '17,34,400,12' list – Amol Gunjal Jan 16 '15 at 11:23
  • @AmolGunjal you can modify the where clause. I converted like into equal to condition. – Deep Jan 16 '15 at 12:39