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)

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

(1 row(s) affected)
--------------
aa
(1 row(s) affected)
--------------
bb
(1 row(s) affected)
--------------
cc
(1 row(s) affected)
--------------

(1 row(s) affected)
Tags:


Share This


 


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>