where clause with values like item1,item2, item3

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

where clause with values like item1,item2, item3

Author

Topic

tpiazza55
Posting Yak Master

162 Posts

Posted - 2010-10-27 : 17:05:53

How can i break up "item1,item2,item3" into a where clause.

something of the form:

where item like item1 and item like item2 etc

tpiazza55
Posting Yak Master

162 Posts

Posted - 2010-10-27 : 17:41:12

for anyone else with this headache

DECLARE @NextString varchar(max)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String varchar(max)
DECLARE @Delimiter VARCHAR(10)

DECLARE @DontDelete varchar(max)

SET @String ='item1,item2,item3'
SET @Delimiter = ','
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)

set @DontDelete = ' AND ('

WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)

set @DontDelete = @DontDelete + ' name <> ''' + @NextString + ''''
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)

If (@pos <> 0)
Set @DontDelete = @DontDelete + ' OR '

END

Set @DontDelete = @DontDelete + ')'
Select @DontDelete

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-10-27 : 17:42:58

This will work when the col looks like "item1,item2,item3" it won't if you want "item1,item4,item2,item3"

declare @table table (Col1 varchar(50))

INSERT INTO @table
SELECT 'item1,item2,item6' UNION
SELECT 'item1,item2' UNION
SELECT 'item1,item2,item4' UNION
SELECT 'item5,item2,item6'

select * from @table where col1 like '%' + 'item1,item2' +'%'

Jim

Everyday I learn something that somebody else already knew

shaggy
Posting Yak Master

248 Posts

Posted - 2010-11-04 : 07:45:19

try this one
create table #tmp (a varchar(max))
insert #tmp select 'LILASGROSRee'

insert #tmp
select 'ALFKI'
union select 'LILAS'
union select 'PERIC'
union select 'HUNGC'
union select 'SAVEA'
union select 'SPLIR'
union select 'LONEP'
union select 'GROSR'
union select 'GROSRee'
union select 'LILASGROSRee'

DECLARE @p VARCHAR(50)
SET @p = 'ALFKI,LILAS,PERIC,HUNGC,SAVEA,SPLIR,LONEP,GROSR'

--Query 1
SELECT CHARINDEX( ',' + a + ',', ',' + @p + ',' ),* from #tmp
WHERE CHARINDEX( ',' + a + ',', ',' + @p + ',' ) > 0 ;

--Query 2
SELECT * FROM #tmp
WHERE ',' + @p + ',' LIKE '%,' + a + ',%' ;

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-04 : 08:56:07

See my solution using fnParseArray here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151609

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources