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.