It is very common to use comma seperated values, especially with CSV files and text files. At times we may have to extract these values and put it in a table. Here I am showing you the SQL script which can be used to achieve this. You can create a stored procedure or function to achieve this
The below set of queries shows how we can seperate comma seperated values and put in to a temp table:
declare @strlength int
declare @temp int
declare @start int
declare @end int
declare @string nvarchar(500)
set @string='B31AA,B31AB,B31AA,B31AB,B31AC,B31AD,B31AE,B31AF'
set @strlength=(select len(@string))
set @temp=0
set @start=0
set @end=1
create table #Corelines(subgroup nvarchar(10))
while @temp<=@strlength-1
begin
if substring(@string,@temp,1)=',' or @temp=@strlength-1
begin
if @temp=@strlength-1
insert #Corelines select substring(@string,@start,(@strlength-@start)+1)
else
insert #Corelines select substring(@string,@start,@end-1)
set @start=@temp+1
set @end=0
end
set @temp=@temp+1
set @end=@end+1
end
select subgroup from #Corelines
drop table #Corelines
The below set of queries shows how we can seperate comma seperated values and put in to a temp table:
declare @strlength int
declare @temp int
declare @start int
declare @end int
declare @string nvarchar(500)
set @string='B31AA,B31AB,B31AA,B31AB,B31AC,B31AD,B31AE,B31AF'
set @strlength=(select len(@string))
set @temp=0
set @start=0
set @end=1
create table #Corelines(subgroup nvarchar(10))
while @temp<=@strlength-1
begin
if substring(@string,@temp,1)=',' or @temp=@strlength-1
begin
if @temp=@strlength-1
insert #Corelines select substring(@string,@start,(@strlength-@start)+1)
else
insert #Corelines select substring(@string,@start,@end-1)
set @start=@temp+1
set @end=0
end
set @temp=@temp+1
set @end=@end+1
end
select subgroup from #Corelines
drop table #Corelines
