Split function(table valued) in sql
The table valued split function split one column based on some Di-limiter and create another table which shows result in column wise.
De limiter can be comma or even white space.
This function can be used any where the requirement is to split the value of column to be used as part in the program.
Split Function in SQL
Return column wise table
My requirement is to convert a single column of a table to another multi column table with the save data that single column contained.
The table structure i have is as follow:-
MidpointId MidPoint
1 3,2,1
2 -2.5,1.2,2
3 12,11,2.9
I only need the x, y , z coordinates of above table in different column.
The table required from this table is as:-
PointId PointX PointY PointZ
1 3 2 1
2 -2.5 1.2 2
3 12 11 2.9
This is a very critical job. After hunting for 3 hours write a Tabled valued function which return table as i required.
The code i have use to write the sql table valued function is as:-
-- =============================================
-- Author:
-- Create date: <27,6,2011>
-- Description:
-- =============================================
alter FUNCTION SplitCommaSeperatedToTable(@String nvarchar(MAX), @Delimiter nvarchar(5))
returns @temptable TABLE (startX nvarchar(20),startY nvarchar(20),startZ nvarchar(20))
as
begin
declare @idx int
declare @slice nvarchar(MAX)
declare @temp TABLE (Items nvarchar(20))
declare @startx nvarchar(20)
declare @starty nvarchar(20)
declare @startz nvarchar(20)
declare @counter int
DECLARE @COUNT INT
SET @COUNT=1
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
BEGIN
-- if(@counter=0)
--BEGIN
-- set @counter=@counter+1
insert into @temp(Items) values(RTRIM(LTRIM(@slice)))
--END
--else if(@counter=1)
-- begin
-- insert into @temptable(startX) values(RTRIM(LTRIM(@slice)))
--end
END
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
BEGIN
SELECT @COUNTER=COUNT(*) FROM @TEMP
WHILE @COUNT<=@COUNTER
BEGIN
select @startx = Items from @temp WHERE @COUNT=1
select @starty=Items from @temp WHERE @COUNT=2
select @startz = Items from @temp WHERE @COUNT=3
SET @COUNT=@COUNT+1
END
END
insert into @temptable (startX,startY,startZ) values (@startx,@starty,@startz)
delete from @temptable where startY is null or startZ is null
end
return
end
All the code above is self explanatory and very simple to understand.
Thanks & Regards
Balwant