Results 1 to 3 of 3

Thread: SQL Server 2008 R2: Split function

  1. #1

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    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

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL Server 2008 R2: Split function

    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!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    Re: SQL Server 2008 R2: Split function

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width