2012-07-18 15:42:35 8 Comments
I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value.
Ex: "one,two,three,four" would return a new table with only one column containing the following values:
one
two
three
four
Does this code look error prone to you guys? When I test it with
SELECT * FROM utvf_Split('one,two,three,four',',')
it just runs forever and never returns anything. This is getting really disheartening especially since there are no built in split functions on MSSQL server (WHY WHY WHY?!) and all the similar functions I've found on the web are absolute trash or simply irrelevant to what I'm trying to do.
Here is the function:
USE *myDBname*
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[utvf_SPlit] (@String VARCHAR(MAX), @delimiter CHAR)
RETURNS @SplitValues TABLE
(
Asset_ID VARCHAR(MAX) NOT NULL
)
AS
BEGIN
DECLARE @FoundIndex INT
DECLARE @ReturnValue VARCHAR(MAX)
SET @FoundIndex = CHARINDEX(@delimiter, @String)
WHILE (@FoundIndex <> 0)
BEGIN
DECLARE @NextFoundIndex INT
SET @NextFoundIndex = CHARINDEX(@delimiter, @String, @FoundIndex+1)
SET @ReturnValue = SUBSTRING(@String, @FoundIndex,@[email protected])
SET @FoundIndex = CHARINDEX(@delimiter, @String)
INSERT @SplitValues (Asset_ID) VALUES (@ReturnValue)
END
RETURN
END
Related Questions
Sponsored Content
2 Answered Questions
[SOLVED] Investigating errors from strange query
- 2017-09-15 13:32:11
- carlo.borreo
- 861 View
- 2 Score
- 2 Answer
- Tags: sql-server sql-server-2016
1 Answered Questions
1 Answered Questions
[SOLVED] Why would call to scalar function inside a Table Value Function be slower than outside the TVF?
- 2014-09-09 22:48:25
- jmoreno
- 962 View
- 4 Score
- 1 Answer
- Tags: sql-server sql-server-2008-r2 performance functions
1 Answered Questions
[SOLVED] Efficiency of Scalar UDF vs TVF
- 2018-07-30 21:18:36
- Thomas D.
- 168 View
- 2 Score
- 1 Answer
- Tags: sql-server optimization functions set-returning-functions
2 Answered Questions
[SOLVED] Joining two inline functions, slows considerably when filtering on second function
- 2015-03-17 16:41:49
- Jweaver
- 1180 View
- 2 Score
- 2 Answer
- Tags: sql-server sql-server-2008-r2 optimization execution-plan functions
1 Answered Questions
[SOLVED] T-SQL View -- How to 'pre-fetch' schema using scalar function, then populate using table query
- 2014-12-05 01:51:25
- mjmoody383
- 446 View
- 1 Score
- 1 Answer
- Tags: sql-server t-sql cte
5 comments
@Erik Darling 2015-02-02 18:44:52
I have been using and loving Jeff Moden's string splitter just about since it came out.
Tally OH! An Improved SQL 8K “CSV Splitter” Function
@Michael Green 2017-11-13 09:02:59
SQL Server 2016 introduced the STRING_SPLIT() function. It has two parameters - the string to be chopped up and the separator. The output is one row per value returned.
For the given example
will return
@Aaron Bertrand 2012-07-18 15:56:05
I wouldn't do this with a loop; there are much better alternatives. By far the best, when you have to split, is CLR, and Adam Machanic's approach is the fastest I've tested.
Next best approach IMHO, if you can't implement CLR, is a numbers table:
... which allows this function:
I believe all of these will perform better than the function you have, when you get it working, especially since they are inline instead of multi-statement. I haven't investigated why yours isn't working, because I don't think it's worth it to get that function working.
But that all said...
Since you are using SQL Server 2008, is there a reason you need to split in the first place? I would rather use a TVP for this:
Now you can accept this as a parameter to your stored procedures, and use the contents just like you would use a TVF:
And you can pass a TVP directly from C# etc. as a DataTable. This will almost certainly outperform any of the solutions above, especially if you are building a comma-separated string in your app specifically so that your stored procedure can call a TVP to split it apart again. For a lot more info on TVPs see Erland Sommarskog's great article.
More recently, I've written a series on splitting strings:
And if you are using SQL Server 2016 or newer (or Azure SQL Database), there is a new
STRING_SPLIT
function, which I blogged about here:@Mudassir 2014-08-27 08:13:00
@Derek Kromm 2012-07-18 15:52:03
Re-worked it slightly...