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

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