How To Split String Using Function in Sql server
How To Split String Using Function in Sql server,
Sql server String Split function, Split function, Delimiter using Split function in sql server, split string function for Sql server, Create function for split string in sql server. Code for Split String Using Function in Sql server.
How to split String in Sql server
Introduction
Sql Server does not (on my knowledge) have in-build Split function.
Split function in general on all platforms would have comma-separated string value to be split into individual strings.
In sql server, the main objective or necessary of the Split function is to convert a comma-separated string value ('abc,cde,fgh') into a temp table with each string as rows.
The below Split function is Table-valued function which would help us splitting comma-separated (or any other delimiter value) string to individual string.
CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
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)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
Simple way of spliting a string with (,) sperated
--------------------------------------------------
Begin
Declare @Fullstring string
Declare @RowStartIndex int
Set @ScopeVariable string
Set @Fullstring='a,b,c,d'
Set @RowStartIndex=0
DECLARE @ResultTable TABLE
(
items char(1)
)
While(Len(@Fullstring)>0)
Begin
if(CharIndex(',',@Fullstring)>0)
Begin
set @ScopeVariable=Substring(@Fullstring,@RowStartIndex,CharIndex(',',@Fullstring)-1)
Set @Fullstring=Replace(@Fullstring,@ScopeVariable+',','')
Insert InTo @ResultTable(items) Values(@ScopeVariable)
End
set @ScopeVariable=''
End
Select * From @ResultTable
End