devxlogo

A SQL Split Function

A SQL Split Function

This SQL function is similar to the VB split function. It takes a nvarchar delimeted list and delimeter and returns a table with the values split on delimeter.

 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UTILfn_Split]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[UTILfn_Split]GO create function dbo.UTILfn_Split( @String nvarchar (4000), @Delimiter nvarchar (10) )returns @ValueTable table ([Value] nvarchar(4000))begin declare @NextString nvarchar(4000) declare @Pos int declare @NextPos int declare @CommaCheck nvarchar(1)  --Initialize set @NextString = '' set @CommaCheck = right(@String,1)   --Check for trailing Comma, if not exists, INSERT --if (@CommaCheck  @Delimiter ) set @String = @String + @Delimiter  --Get position of first Comma set @Pos = charindex(@Delimiter,@String) set @NextPos = 1  --Loop while there is still a comma in the String of levels while (@pos   0)   begin  set @NextString = substring(@String,1,@Pos - 1)   insert into @ValueTable ( [Value]) Values (@NextString)   set @String = substring(@String,@pos +1,len(@String))    set @NextPos = @Pos  set @pos  = charindex(@Delimiter,@String) end  returnend

devx-admin

Share the Post: