November 12, 2009 at 12:28 pm
Comments posted to this topic are about the item syscolumns names
December 2, 2009 at 2:22 am
Considering the simplicity and apparent history of working reliably in several places, I found a surprisingly large number of finer points in the submitted code. The asymmetry in trimming was what bothered me the most.
I removed some fluff such as extra CONTINUE which was exactly as useful here as it is in all the silly WHILE examples in BOL (might be why it had succeeded in sneaking into the code). Also, a set of one members is now handled like any other last element and not as a special case (with its own trimming issue in the original code). These minor issues demonstrate just how dangerous trusted, working, legacy code can be. This looks like it has been used during several years, a workhorse that has seldom let anyone down. And then comes a newbie with his edge cases 🙂
My version:
CREATE FUNCTION [dbo].[Udf_TABLECOLUMNLIST]
(
@strDataSet VARCHAR(4000)
)
RETURNS @Dst TABLE
(
x SYSNAME
)
AS
BEGIN
DECLARE @Idxb INT
IF @strDataSet IS NULL RETURN -- a rowset having zero rows
SET @Idxb=CHARINDEX(',',@strDataSet,1)
WHILE @Idxb<>0
BEGIN
INSERT INTO @Dst
SELECT CAST(LTRIM(RTRIM(SUBSTRING(@strDataSet,1,@Idxb-1))) AS SYSNAME)
SELECT @strDataSet=LTRIM(RTRIM(SUBSTRING(@strDataSet,@Idxb+1,LEN(@strDataSet)-@Idxb)))
SET @Idxb=CHARINDEX(',',@strDataSet,1)
END
INSERT INTO @Dst
SELECT CAST(LTRIM(RTRIM(@strDataSet)) AS SYSNAME)
RETURN
END
GO
SELECT X, LEN(X) AS L FROM dbo.Udf_TABLECOLUMNLIST (' a , b ')
UNION ALL
SELECT X, LEN(X) AS L FROM dbo.Udf_TABLECOLUMNLIST (' c ')
I added the function to my SQL toolbox. Thanks.
December 2, 2009 at 2:27 am
No need for a while loop to split delimited strings. See Jeff Modens Tally table article, linked to in my sig below, for some great tips on avoiding while loops in this, and many other situations.
December 2, 2009 at 3:32 am
This is what fascinates me whenever working with SQL - there are always dozens of alternatives 😉
I analyzed recently some ways to parse a string to a table for performance - with long strings the methods tend to become quite expensive.
The winner from CPU point of view was the XML method, this goes like this:
declare @strDataSet as varchar(max);
declare @delimiter as varchar(10);
set @strDataSet='A,B,C,D,E';
set @delimiter =',';
declare @xml as xml;
set @xml = cast(('<X>'+replace(@strDataSet,@delimiter ,'</X><X>')+'</X>') as xml);
select N.value('.', 'sysname') as x from @xml.nodes('X') as T(N);
Here some performance results for a string with 2361 delimited elements...
1: XML (see above) - SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 25 ms.
(2361 row(s) affected)
2: #Temp (Bernabe's way) - SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 278 ms.
(2361 row(s) affected)
3: CTE - SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 467 ms.
Msg 530, Level 16, State 1, Line 19
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
As you see, CTE has limitations and is rather expensive - but that XML is that much faster than stirng functions in a loop, was a surprise to me...
Cheers, Richard.
December 2, 2009 at 4:15 am
Richard,
Try out Jeffs tally table method, if you haven't already, you should find it's even faster.
I got the following times on a 9000 element source string.
------------ XML Method ------------
(9000 row(s) affected)
SQL Server Execution Times:
CPU time = 235 ms, elapsed time = 228 ms.
------------ Tally table method ------------
(9000 row(s) affected)
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 159 ms.
December 2, 2009 at 9:11 am
Thanx for your hint, Nigel,
the Tally Table method was new to me, but I like this - sleek and fast!
Performance differences are varying - I get slightly different values with every execution - but generally the XML and the Tally Methods are really performant.
If the tally table must be created first than probably there is not much difference, but...
If you happen to have your numbers table somewhere in the DB - the tally is indeed even faster than any other solution I tried!
Thank you again for your valuable contribution!
Cheers, Richard.
December 2, 2009 at 9:33 am
Richard,
No problem at all.
There's actually a big thread (almost 500 posts) here discussing the various methods of splitting strings that's worth looking at.
If you have the time, that is 🙂
May 13, 2010 at 8:47 pm
I like the xml way, then I worked on that version
Here is another way thanks to this forum. It is a generalize version.
I prepared this version (my original did not used XML)
By being helped by forums feedback from SQL central
Forward it to yours peers.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Bernabé Díaz
-- Create date: 04/08/2010
-- Description: To return a list from an string
-- =============================================
CREATE PROCEDURE Usp_GetTableList
@strDataSet AS VARCHAR(max)
,@delimiter AS VARCHAR(10)=','
,@Rt AS VARCHAR(128)='SYSNAME'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @STR VARCHAR(MAX);
SET @strDataSet=ISNULL(@strDataSet,'')
SET @STR=
'
DECLARE @xml AS XML;
SET @xml = CAST((''<X>''+REPLACE('''+@strDataSet+''','''+@delimiter+''' ,''</X><X>'')+''</X>'') AS XML);
SELECT N.value(''.'', '''+@Rt+''') AS x FROM @xml.nodes(''X'') AS MyTable(N);
'
EXEC(@STR)
END
May 23, 2016 at 7:02 am
Thanks for the script.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply