Split() function for Microsoft MSSQL

Microsoft MSSQL doesn’t have a split() function (like in Ruby, Perl), so here’s one I wrote. It’s different from the other MSSQL split() functions out there on the intertubes in that it accepts a @pos parameter that specifies which split item to return – easier than trying to tangle with tables inside a calling function when you want the n’th split item.

CREATE FUNCTION [dbo].[split]
(
    @string varchar(MAX),
    @delimiter CHAR(1),
    @pos INT 
)
RETURNS varchar(255)
AS
BEGIN
    DECLARE @start INT, @end INT, @count INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string), @count = 1 
    WHILE @start < LEN(@string) + 1 BEGIN
        IF @end = 0 
            SET @end = LEN(@string) + 1 

        IF @count = @pos
            RETURN SUBSTRING(@string, @start, @end - @start)

        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        SET @count = @count + 1 

    END 
    RETURN '' -- not found
END

select dbo.split('aa,bb,cc', ',', 0)
select dbo.split('aa,bb,cc', ',', 1)
select dbo.split('aa,bb,cc', ',', 2)
select dbo.split('aa,bb,cc', ',', 3)
select dbo.split('aa,bb,cc', ',', 4)

--------------

                   # 0: zero length string
(1 row(s) affected)
--------------
aa                 # 1
(1 row(s) affected)
--------------
bb                 # 2
(1 row(s) affected)
--------------
cc                 # 3
(1 row(s) affected)
--------------
                   # 4: zero length string
(1 row(s) affected)
Tags:


Share This


 


2 Responses to Split() function for Microsoft MSSQL

  1. Thanks a bunch for this code. I was fighting with using SUBSTRINGs for a select statement to extract parts of a delimited data in a column and this made things so much easier.
    Your name is credited with the function for my deployment. Thx

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>