How to get comma separated values into rows with having multiple comma separated value inside brackets

Question

I have a string like:

a,b,c(a,b,c),d,e,L(e,d,g)

And I want it as rows like:

1. a
2. b
3. c(a,b,c)
4. d
5. e
6. L(e,d,g)

In MSSQL query.

Answer 1

Well you can use UNION or create table and insert such data there.

Union Example

SELECT 'a' FROM dual
UNION
SELECT 'b' FROM dual  
UNION
SELECT 'c(a,b,c)' FROM dual

and so on.

@EDIT:

Understood, based on comment you could peek on answer here: https://stackoverflow.com/a/5493616/8416043

I will not multiplicate it here.

good luck

Answer 2

IF OBJECT_ID('tempdb..#Data')IS NOT NULL
DROP TABLE #Data

CREATE TABLE #Data
(
    ID INT IDENTITY(1,1),
    String VARCHAR(MAX)
)


INSERT #Data SELECT 'a,b,c(a,b,c),d,e,L(e,d,g)'

UPDATE T SET String=REPLACE(REPLACE(String,'(','('''),')',''')') FROM #Data T

IF OBJECT_ID('tempdb..#T')IS NOT NULL
DROP TABLE #T

DECLARE @FieldSeparator VARCHAR(10)='(',@FieldSeparator1 VARCHAR(10)=')'

;WITH CTE AS
(
    SELECT
        ID,
        LEFT(String, CHARINDEX(@FieldSeparator, String + @FieldSeparator) - 1) DataItem,
        STUFF(String, 1, CHARINDEX(@FieldSeparator, String + @FieldSeparator), '') String
    FROM #Data
    UNION all
    SELECT
        ID,
        LEFT(String, CHARINDEX(@FieldSeparator, String + @FieldSeparator) - 1),
        STUFF(String, 1, CHARINDEX(@FieldSeparator, String + @FieldSeparator), '')
    FROM CTE
    WHERE String > ''
)
SELECT
    ID,
    DataItem AS String
INTO #T
FROM CTE WHERE CHARINDEX(@FieldSeparator1,DataItem)>0
ORDER BY ID


--SELECT * FROM #T

IF OBJECT_ID('tempdb..#T1')IS NOT NULL
DROP TABLE #T1

;WITH CTE AS
(
    SELECT
        ID,
        LEFT(String, CHARINDEX(@FieldSeparator1, String + @FieldSeparator1) - 1) DataItem,
        STUFF(String, 1, CHARINDEX(@FieldSeparator1, String + @FieldSeparator1), '') String
    FROM #T
    UNION ALL
    SELECT
        ID,
        LEFT(String, CHARINDEX(@FieldSeparator1, String + @FieldSeparator1) - 1),
        STUFF(String, 1, CHARINDEX(@FieldSeparator1, String + @FieldSeparator1), '')
    FROM CTE
    WHERE String > ''
)
SELECT
    ID,
    DataItem,
                DataItem NewString 
INTO #T1
FROM CTE WHERE CHARINDEX('''',DataItem)>0
ORDER BY ID

--SELECT * FROM #T1

UPDATE #T1 SET NewString=REPLACE(REPLACE(NewString,',','|'),'''','')

UPDATE #T1 SET DataItem='('+DataItem+')',NewString='('+NewString+')'

ALTER TABLE #T1 ADD Rn INT

UPDATE A SET A.Rn=Rn1 FROM
(
SELECT *,ROW_NUMBER()OVER(ORDER BY ID ASC)Rn1 FROM #T1 
)A


DECLARE @Start INT =1,@End INT=(SELECT MAX(Rn) FROM #T1),@Str VARCHAR(1000),@Id INT


WHILE @Start<=@End
BEGIN 

                UPDATE T SET T.String=REPLACE(T.String,T1.DataItem,T1.NewString) FROM #Data T
                INNER JOIN #T1 T1 ON T.ID=T1.Id AND T1.Rn=@Start

                SET @Start=@Start+1

END


--SELECT * FROM #Data
DECLARE @FieldSeparator2 VARCHAR(10)=','

;WITH CTE AS
(
    SELECT
        ID,
        LEFT(String, CHARINDEX(@FieldSeparator2, String + @FieldSeparator2) - 1) DataItem,
        STUFF(String, 1, CHARINDEX(@FieldSeparator2, String + @FieldSeparator2), '') String
    FROM #Data
    UNION all
    SELECT
        ID,
        LEFT(String, CHARINDEX(@FieldSeparator2, String + @FieldSeparator2) - 1),
        STUFF(String, 1, CHARINDEX(@FieldSeparator2, String + @FieldSeparator2), '')
    FROM CTE
    WHERE String > ''
)
SELECT
    ID,
    REPLACE(REPLACE(DataItem,'|',','),'''','') AS String
FROM CTE 
ORDER BY ID

Answer 3

Anything's possible with enough creative effort, but SQL Server really isn't the place to be doing complex text manipulation:

declare @start varchar(max) = 'a,b,c(a,b,c),d,e,L(e,d,g)'

declare @inter1 varchar(max) = REPLACE(REPLACE(@start,'(','<b><a>'),')','</a></b>')

declare @inter2 varchar(max) = '<c><a>' + REPLACE(@inter1,',','</a><a>') + '</a></c>'

select REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(varchar(max),x.query('.')),'</a><a>',','),'<b><a>','('),'</a></b>',')'),'</a>',''),'<a>','')
from (select CONVERT(xml,@inter2)) t (abc)
cross apply abc.nodes('/c/a') u (x)

This works by morphing the string into XML, using XML shredding that just operates against the (transformed) non-nested commas, and then undoing the XML-ification to restore back to the original.

I've done it as a couple of separate steps so that you can see the intermediate stages of the XML-ification if you want to.

Answer 4

Use below function to get row list. It will help

create FUNCTION [dbo].[spliting_function]
(
    @p_string VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@p_string,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@p_string,1,CHARINDEX(@sDelimiter  ,@p_string,0)-1))),
  @p_string=RTRIM(LTRIM(SUBSTRING(@p_string,CHARINDEX(@sDelimiter,@p_string,0)+LEN(@sDelimiter),LEN(@p_string))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@p_string) > 0
 INSERT INTO @List SELECT @p_string -- Put the last item in
RETURN
END


select item from dbo.spliting_function('a,b,c',',')