You need a custom SPLIT function to handle your not well-designed data for your queries. In stead of struggling with each query, I would suggest you redsign your table in a normalized form for easy data retrieval.
--first of all you will need to create this user defined function
GO
--function start
CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
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(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
GO
--function end
GO
--now check below sample
--table variable to hold data
declare @table table
(
id int,[key] varchar(100)
)
insert into @table
select 1,'session, state' union all
select 2,'a,b,c' union all
select 3,'hi, hello'
--parameter value
declare @data varchar(100)
set @data='b,c'
--select query to find data
select distinct id
from @table
CROSS APPLY dbo.Split([key],',') AS AA
CROSS APPLY dbo.Split(@data,',') AS BB
WHERE AA.items=BB.items
--output
--id
--2
cchidambaram
Member
136 Points
115 Posts
how to compare comma separated values with comma separated values in sql
May 02, 2012 02:17 PM|LINK
hi everyone,
I have a table with a column having the values comma seperated as follows.
input string='b,c';
now i need to get the id=2 where key LIKE input.
thank u
TabAlleman
All-Star
15571 Points
2700 Posts
Re: how to compare comma separated values with comma separated values in sql
May 02, 2012 02:19 PM|LINK
SELECT id FROM table WHERE key LIKE '%b,c%'
cchidambaram
Member
136 Points
115 Posts
Re: how to compare comma separated values with comma separated values in sql
May 02, 2012 02:32 PM|LINK
input string='b,c';
limno
All-Star
117326 Points
8003 Posts
Moderator
MVP
Re: how to compare comma separated values with comma separated values in sql
May 02, 2012 03:51 PM|LINK
You need a custom SPLIT function to handle your not well-designed data for your queries. In stead of struggling with each query, I would suggest you redsign your table in a normalized form for easy data retrieval.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
yrb.yogi
Star
14460 Points
2402 Posts
Re: how to compare comma separated values with comma separated values in sql
May 03, 2012 12:11 PM|LINK
--first of all you will need to create this user defined function GO --function start CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(8000)) 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(Items) values(@slice) set @String = right(@String,len(@String) - @idx) if len(@String) = 0 break end return end GO --function end GO --now check below sample --table variable to hold data declare @table table ( id int,[key] varchar(100) ) insert into @table select 1,'session, state' union all select 2,'a,b,c' union all select 3,'hi, hello' --parameter value declare @data varchar(100) set @data='b,c' --select query to find data select distinct id from @table CROSS APPLY dbo.Split([key],',') AS AA CROSS APPLY dbo.Split(@data,',') AS BB WHERE AA.items=BB.items --output --id --2.Net All About