SQL String Parsing Function

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

go

CREATE FUNCTION [dbo].[fn_SplitGuidIds](
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
RETURNS @GuidIdTable TABLE ([GuidId] uniqueidentifier)
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 @GuidIdTable ( [GuidId]) VALUES (@NextString )
SET @String = substring(@String,@pos +1,len(@String))
SET @NextPos = @Pos
SET @pos = charindex(@Delimiter,@String)
END

RETURN
END

Advertisements

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