Help with parsing a comma delimited field

[DanC]

I tried searching and couldn't really find an answer to what I needed, so here goes:

I've got a field called type_list that is being passed into a cursor. This field is varchar and can contain single values or unlimited values seperated by a comma, like this: 1,2,3 . I then need to be able to pass that cursor value into a where clause for a statement, something like this: select * from table1 where type in ('1', '2', '3'). I'm trying to write something that parses the field while being dynamically based on the number of commas. If anyone can point me in the proper direction, I would really appreciate it.

maybe this explains it a little more clearly:

declare @list varchar(100)
set @list = '1,2,3,4'
--missing code to transform to proper form --
select * from table1 where type in @list

which needs to look like

select * from table1 where type in ('1','2','3','4') when it's done.

thanks in advance

-Dan

 

[swampBoogie]

Well, you can either use dynamic sql

declare @list varchar(100)
set @list = '1,2,3,4'

exec ('select * from table1 where type in (' + @list + ')')

or write an UDF that returns a table variable

or (imo the best) redesign the database and not store multiple values in a column.

 

[DanC]

thanks for the quick reply, #3 is definately the best answer, unfortunately this is an older version of our software on which development has essentially been halted. The problem has been resolved in our newer databases.

#1 doesnt' work due to the fact that it's a varchar field that I'm searching on and can contain both letters and numbers. The variable needs to be converted from 1,2,3,4 to '1','2','3','4'.

I'll have to look into #2

thanks

 

[btgroup]

You can change the value as follows:

declare @list varchar(100)
set @list = '1,2,3,4'

set @list = '''' + replace(@list, ',', ''',''') + ''''

 

[ClaireHCM]

The " and' should be placed correctly.

declare @list varchar(100)
set @list = '1,2,3,4'
set @list=replace(@list,',','","')
exec('select * from pre where orderno in ("' + @list + '&quot')

 

[DanC]

well, the replace didn't quite work either, but I managed to figure it out:

declare @type varchar(20)
declare @newtype varchar(20)
declare @t1 varchar(2)

set @list = '1,2,3'

while charindex(',', @type)>0

begin
set @t1=(SELECT left(@type,charindex(',',@type)-1))
set @type=substring(@type,charindex(',', @type)+1,100)
set @newtype = isnull(@newtype,'') + '''' + @t1 + ''','
if charindex(',', @type)=0
break
else
continue
end
set @t1 = @type
set @newtype = '(' + @newtype + '''' + @t1 + '''' + ')'

takes the variable @type with the value of '1,2,3' and converts it to a new variable with the value of ('1','2','3')

thanks for the other suggestions.

 

[DanC]

sorry, the set @list = '1,2,3' should read set @type = '1,2,3'