1

I have serialized data stored in a column. I want to convert list values to a temp table. But there are more than one factors in row values like

["2","3","4"]
["1","2","3"]
[]
["Select option B","Select option C","Select option D"]
["Moderate","Heavy","Heavy, Big & Abnormal"]

If I parse out double quotes then comma in string value will create that as different entity.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
CFML_Developer
  • 1,565
  • 7
  • 18
  • 1
    Sample data is best served as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language). Please [edit] your question to include it, your current attempt and your desired results. For more details, [read this.](https://dba.meta.stackexchange.com/questions/2976/help-me-write-this-query-in-sql) – Zohar Peled Mar 31 '19 at 10:12

2 Answers2

2

This is tagged with [sql-server-2012] - what a pity... With v2016+ you could call for STRING_SPLIT or even JSON methods... The following is a rather hacky approach but works - at least with your provided test data...

Create a mockup-table (please do this yourself the next time).

DECLARE @tbl TABLE(ID INT IDENTITY, YourString VARCHAR(100));
INSERT INTO @tbl VALUES
 ('["2","3","4"]')
,('["1","2","3"]')
,('[]')
,('["Select option B","Select option C","Select option D"]')
,('["Moderate","Heavy","Heavy, Big & Abnormal"]');

--This is the query:

SELECT t.ID
      --,t.YourString 
      ,C.Separated.value('text()[1]','nvarchar(max)') AS Parted
FROM @tbl t
CROSS APPLY(SELECT REPLACE(REPLACE(REPLACE(YourString,'"','"'),'["',''),'"]','')) A(replaced)
CROSS APPLY(SELECT CAST('<x>' + REPLACE((SELECT A.replaced [*] FOR XML PATH('')),'","','</x><x>') + '</x>' AS XML)) B(casted)
CROSS APPLY B.casted.nodes('/x') C(Separated);

The idea in short:

First of all I use multiple REPLACE() to clean and harmonise your data. The second CROSS APPLY will then use XML to split up your strings by replacing each comma together with the quotes! with XML tags. Thus we can prevent splitting at internal commas. But before, we have to use FOR XML on the orginal string, to allow characters such as the & in Big & Abnormal. The rest ist rather easy XPath/XQuery.

The result

+----+-----------------------+
| ID | Parted                |
+----+-----------------------+
| 1  | 2                     |
+----+-----------------------+
| 1  | 3                     |
+----+-----------------------+
| 1  | 4                     |
+----+-----------------------+
| 2  | 1                     |
+----+-----------------------+
| 2  | 2                     |
+----+-----------------------+
| 2  | 3                     |
+----+-----------------------+
| 3  | []                    |
+----+-----------------------+
| 4  | Select option B       |
+----+-----------------------+
| 4  | Select option C       |
+----+-----------------------+
| 4  | Select option D       |
+----+-----------------------+
| 5  | Moderate              |
+----+-----------------------+
| 5  | Heavy                 |
+----+-----------------------+
| 5  | Heavy, Big & Abnormal |
+----+-----------------------+
Shnugo
  • 66,100
  • 9
  • 53
  • 114
0

Using STRING_SPLIT with ordinality:

WITH cte AS (
  SELECT value AS val, ROW_NUMBER() OVER(ORDER BY 1/0) AS ord
  FROM STRING_SPLIT(TRIM('[]' FROM '["2","3","4"]'), ',')
), cte2 AS (
  SELECT value AS val, ROW_NUMBER() OVER(ORDER BY 1/0) AS ord
  FROM STRING_SPLIT(TRIM('[]' FROM '["Select option B","Select option C","Select option D"]'), ',')
)
-- ...
SELECT cte.val, cte2.val
FROM cte
LEFT JOIN cte2
  ON cte.ord = cte2.ord
-- ...

db<>fiddle demo

For SQL Server you could use user defined split function: STRING_SPLIT in SQL Server 2012

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275