The SQL Split Function is
use to SPLIT a string based on the Delimeter such as comma,hyphen etc.
Delimeter is a string
character which is used to identify substring limits in the given string.
SQL Function to split a
string:
create function [dbo].[split2](@id varchar(1000),@sym char)
returns @result table(id varchar(12))
as
begin
declare
@len int
declare
@lenstr varchar(1000)
declare
@charindex int
declare
@splindex int
declare
@startindex int
declare
@lenstr1 int
set
@lenstr1=1
set
@lenstr=@id
while(@lenstr1!=0)
begin
if(charindex(@sym,@lenstr)!=0)
begin
set @charindex=(charindex(@sym,@lenstr)-1)
set @splindex=len(@lenstr)-(charindex(@sym,@lenstr))
set @startindex=charindex(@sym,@lenstr)+1
end
else
begin
set @charindex=len(@lenstr)
set @startindex=1
set @splindex=len(@lenstr)
set @lenstr1=0
end
if ((substring(@lenstr,1,@charindex))!='')
begin
insert into @result(id)values(substring(@lenstr,1,@charindex))
end
set @lenstr=substring(@lenstr,@startindex,@splindex)
end
return
end
Execution Query:
select * from Split2('a,e,y,k,a,y',',')
Result:
