Separation of column value using delimiter
In this article,I explain how to separate the column value by using a delimiter into different columns .
The following article contains one sample table,function used to split and a procedure to
separate the column value.
This is the sample table.
create table #temp1(data varchar(50))
insert into #temp1 values('12-3564-2357')
insert into #temp1 values('566-89-56623')
insert into #temp1 values('23578-2356541-2')
goThis is the Split Function used to spilt the column value using a delimiter.
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME = N'Split'
)
DROP FUNCTION Split
go
create FUNCTION dbo.Split
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
--SET @tempItemList = REPLACE (@tempItemList,' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
GOHere is another table with columns to insert the splitted values and
#temp3 is the original table with slno.
create table #temp2(a varchar(50),b varchar(50),c varchar(50))
select ROW_NUMBER() over (order by data) as slno,data into #temp3 from #temp1
goThis is the Procedure used to separate the column value into different columns.
alter procedure separate_sp
as
begin
declare @i int,@j int,@row_count int,@row_count1 int,@data varchar(50),@a varchar(50),@b varchar(50),@c varchar(50)
set @i=1
set @j=1
truncate table #temp2
select @row_count=COUNT(*) from #TEMP3
while @i<=@row_count
begin
select @data=data from #temp3 where slno=@i
drop table #temp4
drop table #temp5
select * into #temp5 from dbo.Split(@data,'-')
select ROW_NUMBER() over(order by item)as slno,item into #temp4 from dbo.Split(@data,'-')
select @row_count1=COUNT(*) from #temp4
while @j<=@row_count1 and @j<=@i
begin
select @a=item from #temp4 where slno=(select slno from #temp4 where item in (select top 1* from #temp5))
select @b=item from #temp4 where slno=(select slno from #temp4 where item in (select top 1* from #temp5 where item not in (select top 1* from #temp5)))
select @c=item from #temp4 where slno=(select slno from #temp4 where item not in (select top 2* from #temp5))
insert into #temp2 values(@a,@b,@c)
set @j=@j+1
end
set @i=@i+1
end
select * from #temp2
endFor execution
exec separate_spOutput:
a b c
12 3564 2357
23578 2356541 2
566 89 56623