SQL Server 2008 R2: Split function

Hi,

I've been using a UDF split function courtesy of 4GuysFromRolla.com for a few years (2004) but I'm currently working on a project where I need to pass in quite a long list (65+) and I'm finding that it's causing a bottle neck and I wondered if anyone had any ideas on how to make it more efficient?

Code:

---

IF Exists (SELECT * FROM sysobjects WHERE ID=OBJECT_ID('Split') AND type IN('FN','TF')) BEGIN
        DROP FUNCTION [dbo].[Split]
END
Go
CREATE FUNCTION dbo.Split
        (
                @List nvarchar(max),
                @SplitOn nvarchar(5)
        ) 
        RETURNS @RtnValue table
        (
                Id int identity(1,1),
                Value nvarchar(100)
        )
        AS 
        BEGIN
                While (Charindex(@SplitOn,@List)>0) Begin
                        Insert Into @RtnValue (value)
                        Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
                        Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
                End

                Insert Into @RtnValue (Value)
                Select Value = ltrim(rtrim(@List))
       
                Return
        END

---

Regards Al

That function is certainly not designed for speed!

First - do you need it to return nvarchar? I just read yesterday that there is a speed loss with nvarchar over varchar...

Also - the @List variable is being repeatedly re-made every time - there is no need for that. Just "remember" the last starting position and step along with the string leaving it intact.

This function - Charindex(@SplitOn,@List) - is being done twice - it should go into a variable and then the variable used in the two substrings.

I'm surprised you got this from 4GuysFromRolla - it is truly a novice attempt at writing SQL!

szlamany,

Thanks very much for your pointers I've changed the function as follows although I'm not sure my changes to step along the @List variable are very efficient I'd appreciate it if you could have a look and let me know if there is a better way of doing this -

Code:

---

IF Exists (SELECT * FROM sysobjects WHERE ID=OBJECT_ID('Split') AND type IN('FN','TF')) BEGIN
        DROP FUNCTION [dbo].[Split]
END
Go
CREATE FUNCTION dbo.Split
        (
                @List varchar(max),
                @SplitOn varchar(5)
        ) 
        RETURNS @RtnValue table
        (
                Id int identity(1,1),
                Value varchar(100)
        )
        AS 
        BEGIN
                DECLARE @IdxS INT=0 -- remember the last index so we know where the start position is for each val in the list
                DECLARE @Idx INT=CHARINDEX(@SplitOn,@List)
                DECLARE @Val VARCHAR(100)

                While (@Idx>0) BEGIN
                        SET @Val=LTrim(RTrim(SubString(@List,@IdxS,@Idx-@IdxS)))
                       
                        Insert Into @RtnValue (value)
                        Select Value = @Val                       
                        SET @IdxS=@Idx+1
                        SET @Idx=CHARINDEX(@SplitOn,@List,@Idx+1)
                        IF @Idx=0 BEGIN SET @Val=LTrim(RTrim(SubString(@List,@IdxS,(LEN(@List)+1)-@IdxS))) END
                End
                Insert Into @RtnValue (Value)
                Select Value = LTrim(RTrim(COALESCE(@Val,@List)))
               
       
                Return
        END

---

Cheers Al