-
Oct 3rd, 2013, 05:03 AM
#1
Thread Starter
Fanatic Member
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
-
Oct 3rd, 2013, 05:20 AM
#2
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!
-
Oct 3rd, 2013, 07:33 AM
#3
Thread Starter
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|