insert rows from a list of elements 1

[mimi2]

hello,
from an input that looks like '1,6,8,10'
i must insert as many rows as there are elements in the list.
the result should be:
A 1
A 6
A 8
A 10
how can i get rid of the comma ?
thanks for any hint.
(this is the first time that i am using sql server.)

 

[rt63]

u will need to write a small code


declare @var varchar(100) , @loc smallint , @val int
set @var = '1,6,8,10'
set @var = @var + ','
set @loc = charindex(',',@var)

while @loc > 0
begin
set @val = convert(int,left(@var,@loc - 1))
Insert into UrTable values('A',@val)
set @var = substring(@var,@loc + 1,100)
set @loc = charindex(',',@var)
end


RT

 

[mimi2]

thanks.
i wrote this, but i struggled with the negative values inside the substring !
WHILE ( Len (@VarList) > 0)
BEGIN

SET @NewVariable = Substring (@VarList,1,CurrentComma - 1)

INSERT INTO #tempTable (ID,NewVariable)
VALUES (@ID,@NewVariable)

SET @VarList = Substring (@VarList, @CurrentComma + 1 , LEN(@VarList) – LEN (@NewVariable) - 1)
@LastComma = @CurrentComma
@CurrentComma = CharIndex((',',@VarList)

END