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 E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to parse a list with delimiter such as csv?

The following Microsoft SQL Server T-SQL table-valued function (user-defined function - UDF) will return a table of sequence numbers and list members by parsing and splitting a delimited list:

-- T-SQL parse a delimited string - mssql parse comma-separated string

-- MSSQL split a delimited string - split comma-limited string into table

-- Parse a string in sql - parse delimited string sql - parse comma-limited list

USE AdventureWorks2008;

GO

-- SQL table-valued user-defined function create - UDF

CREATE FUNCTION fnListParseAndSplit

               (@DelimiterList VARCHAR(MAX),

                @Delimiter     CHAR(1))

RETURNS @ListTable TABLE(PositionNo INT    NOT NULL,

                         ListMember VARCHAR(MAX)    NOT NULL)

AS

  BEGIN

    DECLARE  @SeqNo             INT,

             @CurrentListMember VARCHAR(MAX),

             @RemainingList     VARCHAR(MAX),

             @Pos               INT

    SET @CurrentListMember = ''

    SET @SeqNo = 1

    SET @RemainingList = @DelimiterList

    SET @Pos = Patindex('%' + @Delimiter + '%',@RemainingList)

    WHILE (@Pos > 0)

      BEGIN

        SET @CurrentListMember = Substring(@RemainingList,1,@Pos - 1)

        

        SET @RemainingList= Substring(@RemainingList,@Pos + 1,Len(@RemainingList) - @Pos)

        

        INSERT @ListTable

              (PositionNo,

               ListMember)

        SELECT @SeqNo,

               @CurrentListMember

        

        SET @Pos = Patindex('%' + @Delimiter + '%',@RemainingList)

        

        SET @SeqNo = @SeqNo + 1

      END

    SET @CurrentListMember = @RemainingList

    IF @CurrentListMember != ''

      INSERT @ListTable

            (PositionNo,

             ListMember)

      SELECT @SeqNo,

             @CurrentListMember

    RETURN

  END

GO

 

-- Parse delimited string function test - Parse comma separated string in SQL test

SELECT *

FROM   fnListParseAndSplit('30.122.211.22','.')

GO

/* Results

 

PositionNo  ListMember

1           30

2           122

3           211

4           22

*/

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

 

-- SQL cross apply usage with table-valued UDF - Parse and Split CompanyName to words

SELECT c.CustomerID, c.CompanyName, w.*

FROM Northwind.dbo.Customers c

CROSS APPLY fnListParseAndSplit(c.CompanyName,' ') w

WHERE LEFT(CustomerID,1) = 'A'

ORDER BY CustomerID, PositionNo

GO

CustomerID CompanyName PositionNo ListMember
ALFKI Alfreds Futterkiste 1 Alfreds
ALFKI Alfreds Futterkiste 2 Futterkiste
ANATR Ana Trujillo Emparedados y helados 1 Ana
ANATR Ana Trujillo Emparedados y helados 2 Trujillo
ANATR Ana Trujillo Emparedados y helados 3 Emparedados
ANATR Ana Trujillo Emparedados y helados 4 y
ANATR Ana Trujillo Emparedados y helados 5 helados
ANTON Antonio Moreno Taquería 1 Antonio
ANTON Antonio Moreno Taquería 2 Moreno
ANTON Antonio Moreno Taquería 3 Taquería
AROUT Around the Horn 1 Around
AROUT Around the Horn 2 the
AROUT Around the Horn 3 Horn

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

Related article:

http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/

 

 

Exam Prep 70-461
Exam 70-461
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