IF object_id('dbo.parseCSV') IS NOT NULL DROP FUNCTION dbo.parseCSV
GO
--parseCSV.sql   Derived from: http://www.sqlservercentral.com/articles/Tally+Table/72993/
/*
We assume input data is comma-separated values with no quotes EXCEPT for fields completely surrounded by quotes.
That is, the opening quote is either column 1 or just after a comma, and the closing quote is either end-of-string or just before a comma.
Usage Example:

	SELECT * FROM dbo.parseCSV('Fat,"10,20,30",and,Lazy')
--*/
CREATE FUNCTION dbo.parseCSV (@pString VARCHAR(8000))
	--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
	RETURNS TABLE WITH SCHEMABINDING AS
	 RETURN
	--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
		 -- enough to cover VARCHAR(8000)
	--You can test by executing from this Declare to the end or an uncommented SELECT after a cte definition.
	--declare @pString varchar(1000)='1,"4,6",9'; set @pString='a1,",xb,",c1';--set @pString='Fat,"10,20,30",and,Lazy';--set @pString='a,b,"c,d"';--set @pString='"1,2","3,4"';
	  WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
					 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
					 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
					)                          --10E+1 or 10 rows
		   ,E2(N) AS (SELECT 1 FROM E1 a, E1 b) --10E+2 or 100 rows
		   ,E4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max
	 ,cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
						 -- for both a performance gain and prevention of accidental "overruns"
					 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
					)
	,cteQtCom AS (--find all quotes and commas
					SELECT t.N
						, CASE WHEN SUBSTRING(@pString,t.N,1)=',' THEN 1 ELSE 0 END [isComma]
						, CASE WHEN SUBSTRING(@pString,t.N,1)='"' THEN 1 ELSE 0 END [isQuote]
					FROM cteTally t
					WHERE SUBSTRING(@pString,t.N,1) IN (',','"')
				)--cteQtCom
			--SELECT * FROM cteQtCom
	,cteNumQuotesBefore AS (
					SELECT t.N, t.isComma, t.isQuote
						, ISNULL((SELECT SUM(t2.isQuote) FROM cteQtCom t2 WHERE t2.N<t.N),0) [qb4]
					FROM cteQtCom t
					)--cteNumQuotesBefore
			--SELECT * FROM cteNumQuotesBefore
	,cteCommasNotWithinQuotes AS (--if the number of quotes before is EVEN then this comma is not inside quotes.
					SELECT t.N
					FROM cteNumQuotesBefore t
					WHERE t.isComma=1 AND (qb4%2)=0
				)--cteCommasNotWithinQuotes
			--SELECT *, SUBSTRING(@pString,1,N)[substr] FROM cteCommasNotWithinQuotes
	,cteStart(N1,this_startquote,prev_endquote) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
					 SELECT 1[N1], CASE WHEN SUBSTRING(@pString,1,1)='"' THEN 1 ELSE 0 END[this_startquote], 0[prev_endquote]
					 UNION ALL
					 SELECT t.N+1[N1]
						, CASE WHEN SUBSTRING(@pString,t.N+1,1)='"' THEN 1 ELSE 0 END[this_startquote]--this field starts with quote
						, CASE WHEN SUBSTRING(@pString,t.N-1,1)='"' THEN 1 ELSE 0 END[prev_endquote]--previous field ended with quote
						--both could be true for contiguous quoted fields: "1","2"
					 FROM cteCommasNotWithinQuotes t
					)--cteStart
			--SELECT * FROM cteStart
	,cteLen AS(--==== Return start and length (for use in substring)
					SELECT s.N1, s.this_startquote
						,CASE WHEN ending_comma.N1 IS NOT NULL THEN ending_comma.N1-s.N1-1
							ELSE LEN(@pString)-s.N1+1 END [L1]
						,CASE WHEN ending_comma.prev_endquote IS NOT NULL THEN ending_comma.prev_endquote
							ELSE CASE WHEN RIGHT(@pString,1)='"' THEN 1 ELSE 0 END END[this_endquote]
					FROM cteStart s
					OUTER APPLY (
						SELECT TOP 1 e.N1,e.prev_endquote 
						FROM cteStart e
						WHERE e.N1>s.N1
						ORDER BY N1
						) ending_comma
					)--cteLen
			--SELECT *,SUBSTRING(@pString,N1,L1)[substr] FROM cteLen
	SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY LL.N1),
		Item       = SUBSTRING(@pString, LL.N1+LL.this_startquote, LL.L1-this_startquote-this_endquote)
	FROM cteLen LL
;
GO

--Create a few hundred test cases by all combinations of A,B,C where each can be bare or "surrounded by quotes"
DECLARE @a TABLE (x VARCHAR(200))
INSERT INTO @a VALUES (''),('a1'),('qa1,qa2'),(',xa,')
DECLARE @q TABLE (iq TINYINT, q VARCHAR(1))
INSERT INTO @q VALUES (0,''),(1,'"')

IF object_id('tempdb..#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (id INT IDENTITY(1,1),s VARCHAR(200))
INSERT INTO #T
	SELECT QA.q+A.x+QA.q
		+','+QB.q+B.x+QB.q
		+','+QC.q+C.x+QC.q
	FROM @a A CROSS JOIN @q QA 
	CROSS JOIN @a B CROSS JOIN @q QB 
	CROSS JOIN @a C CROSS JOIN @q QC


--Run the test -- 512 input rows, 2112 output rows -- up to you to eyeball the results
IF 1=0
	SELECT id,s, r.*
	FROM #T
	OUTER APPLY dbo.parseCSV(s) r

--show fields in separate columns so more compact:
SELECT s, [1][one],[2][two],[3][three],[4][four],[5][five],[6][six],[7][seven]
FROM (
	SELECT t.id, t.s, r.ItemNumber, r.Item
	FROM #T t
	OUTER APPLY dbo.parseCSV(s) r
	) AS SRC
PIVOT (
	MAX(Item)
	FOR ItemNumber IN ([1],[2],[3],[4],[5],[6],[7])
) AS pvt


IF 1=0 BEGIN--testa --prints as we go, so we can find where a problem happened:
	DECLARE @n INT=0
	WHILE EXISTS(SELECT * FROM #T WHERE id>@n) BEGIN--n
		SELECT @n=MIN(id) FROM #T WHERE id>@n
		DECLARE @s1 VARCHAR(200)
		SELECT @s1=s FROM #T WHERE id=@n
		PRINT CONVERT(VARCHAR,@n)+': '+@s1
		SELECT @n[n],* FROM dbo.parseCSV(@s1) v
	END--n
END--testa

CREATE FUNCTION [dbo].[ParseString]
 (
 @String VarChar(8000),
 @Delimiter VarChar(1)
 ) RETURNS TABLE
AS

RETURN
 (
 WITH Tokens(LinePos, StartPos, EndPos) AS 
(
 SELECT 
1, 
1, 
CharIndex(@Delimiter, @String)
 UNION ALL
 SELECT
 LinePos + 1, 
EndPos + 1, 
CharIndex(@Delimiter, @String, EndPos + 1)
 FROM 
Tokens
 WHERE 
EndPos > 0
 )
 SELECT
 CONVERT(Int, LinePos) AS RowNumber,
 SubString
 (
 @String, 
StartPos, 
CASE 
WHEN EndPos > 0 THEN (EndPos - StartPos)
 ELSE 8000 
END
 ) 
AS StringValue
 from Tokens
 )