Split function for SQL Server

CREATE FUNCTION [dbo].[fn_split] (
@Text VARCHAR(MAX),
@Separator VARCHAR(10)
)
RETURNS @Table TABLE (Token VARCHAR(MAX))
AS
BEGIN

DECLARE @StartPosition INT;
DECLARE @EndPosition INT;

SET @StartPosition = 1;
SET @EndPosition = CHARINDEX(@Separator, @Text, @StartPosition);

WHILE (@EndPosition > 0)
BEGIN
INSERT INTO @Table (Token) VALUES (SUBSTRING(@Text, @StartPosition, @EndPosition - @StartPosition));

SET @StartPosition = @EndPosition + LEN(@Separator);
SET @EndPosition = CHARINDEX(@Separator, @Text, @StartPosition);
END; -- WHILE

INSERT INTO @Table (Token) VALUES (SUBSTRING(@Text, @StartPosition, LEN(@Text) - @StartPosition + 1));

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