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]
create function dbo.UTILfn_Split(
@String nvarchar (4000),
@Delimiter nvarchar (10)
returns @ValueTable table ([Value] nvarchar(4000))
declare @NextString nvarchar(4000)
declare @Pos int
declare @NextPos int
declare @CommaCheck nvarchar(1)
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)
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)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: