Q:

Splitting delimited text containing quoted delimiters in SQL Server

This follows on from Passing a varchar full of comma delimited values to a SQL Server IN function. 
I want to split some comma-separated text but I need to allow for embedded commas:
  DECLARE @text NVARCHAR(1000) = 'abc,def,"ghi,jkl",mno';

The results I'm expecting are:
  abc
  def
  ghi,jkl
  mno

Here's the function I use to split CSV text.   
It uses a loop so if performance is an issue you can adapt it using the suggestions here: https://stackoverflow.com/a/878964/482595
CREATE FUNCTION uf_Split
( 
    @Text NVARCHAR(MAX), 
    @Delimiter CHAR(1),
    @Quote CHAR(1)
) 
RETURNS @Result TABLE 
( 
    [Index] INT NOT NULL IDENTITY(1, 1), 
    [Value] NVARCHAR(4000) NULL,
    [CharPos] INT
)
AS 
BEGIN 
    DECLARE @start BIGINT; SET @start = 1
    DECLARE @end BIGINT; SET @end = 1

    IF @Text is null
    BEGIN
      RETURN
    END 

    WHILE 1=1 
    BEGIN 
        SET @end = 
            CASE
                WHEN CHARINDEX(@Quote, @Text, @start) = @start THEN CHARINDEX(@Quote + @Delimiter, @Text, @start + 1)
                ELSE CHARINDEX(@Delimiter, @Text, @start)
            END

        IF ISNULL(@end, 0) = 0 
        BEGIN 
            -- Delimiter could not be found in the remainder of the text:
            INSERT @Result([Value], [CharPos]) VALUES(SUBSTRING(@Text, @start, DATALENGTH(@Text)), @start)
            BREAK 
        END 
        ELSE IF (CHARINDEX(@Quote, @Text, @start) = @start) AND (CHARINDEX(@Quote + @Delimiter, @Text, @start + 1) = @end)
        BEGIN
            INSERT @Result([Value], [CharPos]) VALUES(SUBSTRING(@Text, @start + 1, @end - @start - 1), @start)
            SET @start = @end + 2
        END
        ELSE 
        BEGIN
            INSERT @Result([Value], [CharPos]) VALUES(SUBSTRING(@Text, @start, @end - @start), @start)
            SET @start = @end + 1
        END
    END 

    RETURN
END
GO

A:

I took a shot using a two-phase split in t-sql.  I am definitely interested to see how others approach this one.   If these strings are large or if you wish to process large rowsets I would look into other options, perhaps BULK INSERT or CLR. 
declare @data nvarchar(1000) = 'abc,def,"ghi,jkl",mno,"yak","yak,123"';

declare @x xml;
select  @x = cast('<d>' + replace(@data, '"', '</d><d>') + '</d>' as xml);

;with c(d,i)
as  (   select  p.n.value('.', ' nvarchar(max)') AS data,
                case
                    when left(p.n.value('.', ' nvarchar(max)'), 1) = ',' then 1
                    when right(p.n.value('.', ' nvarchar(max)'), 1) = ',' then 1 
                    else 0 
                end
        from    @x.nodes('/d') p(n)
    )       
select  d
from    c
where   i = 0 and len(d) > 0
union all
select  p.n.value('.', ' nvarchar(max)')
from    (   select cast('<d>' + replace(d, ',', '</d><d>') + '</d>' as xml)
            from c
            where   i=1
        ) d(x)
cross
apply   d.x.nodes('/d')p(n)
where   len(p.n.value('.', ' nvarchar(max)')) > 0;

