Wednesday, June 23, 2010

MSSQL SPLIT Function


CREATE FUNCTION [dbo].[SPLIT]
(
        @inputString NVARCHAR(max),
        @delimiter CHAR
)
RETURNS @result TABLE (token NVARCHAR(max))
AS
BEGIN

DECLARE @startposition INT
DECLARE @endposition INT
DECLARE @inputLength INT
DECLARE @lengthToParse INT

SET @startposition = 1
SET @endposition = 1
SET @inputLength = LEN(@inputString)

WHILE @startposition <= @inputLength 
    BEGIN 


        SELECT @endpositioncharindex(@delimiter,@inputString,@startposition)  


        IF @endposition <> 0
        BEGIN
              SET @endposition = @endposition - 1
        END
        ELSE
        BEGIN
              SET @endposition = @inputLength
       END

       SET @lengthToParse = @endposition - @startposition + 1
       INSERT INTO @result(tokenVALUES(substring(@inputString,@startposition,@lengthToParse))
       SET @startposition = @endposition + 2

END

RETURN

END

Sample usage:

SELECT * FROM SPLIT('abc.def.ghi','.')

Will return:
abc
def
ghi

 

No comments:

Post a Comment