DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS

SQL Server Training Scripts

Parse & Split Comma Limited (CSV) List

The following table-valued functions convert a comma limited list into a table:

   1. dbo.fnSplitCSV -- applying charindex on number list

   2. dbo.fnSplitCSVxml -- applying xml operations on number list

   3. dbo.fnSplitStringList -- applying charindex on string list


-- Split comma-limited number list with the charindex function

 

-- Table-valued user-defined function

CREATE FUNCTION dbo.fnSplitCSV ( @NumberList varchar(4096))

RETURNS @SplitList TABLE (    ListMember INT )

AS

BEGIN

      DECLARE @Pointer int, @ListMember varchar(25)

      SET @NumberList = LTRIM(RTRIM(@NumberList))

      IF (RIGHT(@NumberList, 1) != ',')

         SET @NumberList=@NumberList+ ','

      SET @Pointer = CHARINDEX(',', @NumberList, 1)

      IF REPLACE(@NumberList, ',', '') <> ''

      BEGIN

            WHILE (@Pointer > 0)

            BEGIN

                  SET @ListMember = LTRIM(RTRIM(LEFT(@NumberList, @Pointer - 1)))

                  IF (@ListMember <> '')

                  INSERT INTO @SplitList

                        VALUES (convert(int,@ListMember))

                  SET @NumberList = RIGHT(@NumberList, LEN(@NumberList) - @Pointer)

                  SET @Pointer = CHARINDEX(',', @NumberList, 1)

            END

      END  

      RETURN

END

GO

 

-- Test

SELECT * FROM dbo.fnSplitCSV ('')

SELECT * FROM dbo.fnSplitCSV ('1000')

SELECT * FROM dbo.fnSplitCSV ('1000,4005')

SELECT * FROM dbo.fnSplitCSV ('1000,7,9')

SELECT * FROM dbo.fnSplitCSV ('1000,  3, 8494, 2329992, 8, 23, 43')

GO

 

-- XML split solution for comma-limited number list

 

-- Table-valued user-defined function

CREATE FUNCTION dbo.fnSplitCSVxml

               (@NumberList VARCHAR(4096))

RETURNS @SplitList TABLE( ListMember INT)

AS

  BEGIN

    DECLARE  @xml XML

    SET @NumberList = LTRIM(RTRIM(@NumberList))

    IF LEN(@NumberList) = 0

      RETURN

    SET @xml = '' + REPLACE(@NumberList,',','') + ''

    INSERT INTO @SplitList

    SELECT x.i.value('.','VARCHAR(MAX)') AS Member

    FROM   @xml.nodes('//n') x(i)

    RETURN

  END

GO

 

-- Test

SELECT * FROM dbo.fnSplitCSVxml ('')

SELECT * FROM dbo.fnSplitCSVxml ('1000')

SELECT * FROM dbo.fnSplitCSVxml ('1000, 1007')

SELECT * FROM dbo.fnSplitCSVxml ('1000,7,9')

SELECT * FROM dbo.fnSplitCSVxml ('1000,  3, 8494, 2329992, 8, 23, 43')

GO

 

-- Split a comma-limited string list

 

-- Table-valued user-defined function

CREATE FUNCTION dbo.fnSplitStringList (@StringList VARCHAR(MAX))

RETURNS @TableList TABLE( StringLiteral VARCHAR(128))

BEGIN

    DECLARE @StartPointer INT, @EndPointer INT

    SELECT @StartPointer = 1, @EndPointer = CHARINDEX(',', @StringList)

    WHILE (@StartPointer < LEN(@StringList) + 1)

    BEGIN

        IF @EndPointer = 0

            SET @EndPointer = LEN(@StringList) + 1

        INSERT INTO @TableList (StringLiteral)

        VALUES(LTRIM(RTRIM(SUBSTRING(@StringList, @StartPointer,

                                     @EndPointer - @StartPointer))))

        SET @StartPointer = @EndPointer + 1

        SET @EndPointer = CHARINDEX(',', @StringList, @StartPointer)

    END -- WHILE

    RETURN

END -- FUNCTION

GO

 

-- Test

SELECT * FROM dbo.fnSplitStringList ('New York, California, Arizona, Texas')

GO

 

SELECT * FROM dbo.fnSplitStringList ('New York, California, Arizona, Texas,')

GO

 

SELECT * FROM dbo.fnSplitStringList ('New York')

 

SELECT * FROM dbo.fnSplitStringList ('Smith, Brown, O''Brien, Sinatra')

GO

 

------------ 

 

 

American Standard in SQL Server Training