CODEWITHSUNDEEP
sqlsql-servert-sql
iceflamez
iceflamez

Reputation: 23

SQL Server - Compare Varchar values using IN

In my table, I have a varchar column whereby multi-values are stored. An example of my table:

RecNum           |  Title   |  Category
-----------------------------------------
wja-2012-000001  |  abcdef  |  4,6
wja-2012-000002  |  qwerty  |  1,3,7
wja-2012-000003  |  asdffg  |   
wja-2012-000004  |  zxcvbb  |  2,7
wja-2012-000005  |  ploiuh  |  3,4,12

The values in the Category column points to another table.

How can I return the relevant rows if I want to retrieve the rows with value 1,3,5,6,8 in the Category column?

When I tried using IN, I get the 'Conversion failed when converting the varchar value '1,3,5,6,8' to data type int' error.

Upvotes: 2

Views: 3146

Answers (4)

user1562231
user1562231

Reputation: 251

you can do like this

declare @var varchar(30); set @var='2,3';

exec('select * from category where Category_Id in ('+@var+')')

Upvotes: 1

Void Ray
Void Ray

Reputation: 10219

As mentioned by others, your table design violates basic database design principles and if there is no way around it, you could normalize the table with little code (example below) and then join away with the other table. Here you go:

Data:

CREATE TABLE data(RecNum varchar(20),Title varchar(10),Category varchar(15))
INSERT INTO data
VALUES('wja-2012-000001','abcdef','4,6'),
('wja-2012-000002','qwerty','1,3,7'),
('wja-2012-000003','asdffg',null),   
('wja-2012-000004','zxcvbb','2,7'),
('wja-2012-000005','ploiuh','3,4,12')

This function takes a comma separated string and returns a table:

CREATE FUNCTION listToTable (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (number)
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
      SELECT @pos = @nextpos
   END
   RETURN
END

Then, you can do something like this to "normalize" the table:

SELECT *
FROM   data m
CROSS  APPLY  listToTable(m.Category) AS t
where Category is not null

And then use the result of the above query to join with the "other" table. For example (i did not test this query):

select * from otherTable a
join listToTable('1,3,5,6,8') b
on a.Category = b.number
join(

    SELECT *
    FROM   data m
    CROSS  APPLY  listToTable(m.Category) AS t
    where Category is not null
    ) c
on a.category = c.number

Upvotes: -1

AnandPhadke
AnandPhadke

Reputation: 13506

Try this solution:

CREATE TABLE test4(RecNum varchar(20),Title varchar(10),Category varchar(15))
INSERT INTO test4
VALUES('wja-2012-000001','abcdef','4,6'),
('wja-2012-000002','qwerty','1,3,7'),
('wja-2012-000003','asdffg',null),   
('wja-2012-000004','zxcvbb','2,7'),
('wja-2012-000005','ploiuh','3,4,12')

select * from test4
Declare @str varchar(25) = '1,3,5,6,8'
;WITH CTE as (select RecNum,Title,Category from test4)
,CTE1 as (
select RecNum,Title,RIGHT(@str,LEN(@str)-CHARINDEX(',',@str,1)) as rem from CTE where category like '%'+LEFT(@str,1)+'%' 
union all
select c.RecNum,c.Title,RIGHT(c1.rem,LEN(c1.rem)-CHARINDEX(',',c1.rem,1)) as rem from CTE1 c1 inner join CTE c 
on c.category like '%'+LEFT(c1.rem,1)+'%' and CHARINDEX(',',c1.rem,1)>0
 )
 select RecNum,Title from CTE1

Upvotes: 0

Glen Hughes
Glen Hughes

Reputation: 4832

Breaking the Categories out into a separate table would be a better design if that's a change you can make... otherwise, you could create a function to split the values into a table of integers like this:

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (id int)
as
begin
    declare @idx int
    declare @slice varchar(8000)

    select @idx = 1
        if len(@String)<1 or @String is null  return

    while @idx!= 0
    begin
        set @idx = charindex(@Delimiter,@String)
        if @idx!=0
            set @slice = left(@String,@idx - 1)
        else
            set @slice = @String

        if(len(@slice)>0)
            insert into @temptable(id) values(convert(int, @slice))

        set @String = right(@String,len(@String) - @idx)
        if len(@String) = 0 break
    end
return
end

Then call it from your query:

SELECT ...
FROM ...
WHERE @SomeID IN (SELECT id FROM dbo.Split(Category, ','))

Or if you're looking to provide a list of categories as an input parameter (such as '1,3,5,6,8'), and return all records in your table that contain at least one of these values, you could use a query like this:

SELECT ...
FROM ...
WHERE
    EXISTS (
        select 1
        from dbo.Split(Category, ',') s1
        join dbo.Split(@SearchValues, ',') s2 ON s1.id = s2.id
    )

Upvotes: 2

Related Questions