http://www.zzzprojects.com/sql-server-split-function-example/

sql-server-string-split-function-example-linq-set-operators.sql

-- CREATE test table
DECLARE @SourceTable TABLE
    (
      RowID INT ,
      DelimitedString VARCHAR(8000)
    )

INSERT  INTO @SourceTable
VALUES  ( 1, 'a;a;b' ),
        ( 2, 'a;b;c;b;b;a;c;a' )


-- DECLARE expression to evaluate
DECLARE @sqlnet SQLNET = SQLNET::New('Split(s, ";").Distinct()')


-- 1 | a
-- 1 | b
-- 2 | a
-- 2 | b
-- 2 | c
SELECT  RowID ,
        Value_1
FROM    @SourceTable
        CROSS APPLY ( SELECT    Value_1
                      FROM      dbo.SQLNET_EvalTVF_1(@sqlnet.ValueString('s',
                                                              DelimitedString))
                    ) AS SplitTable
ORDER BY RowID