datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update

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