Get unique values from nvarchar datatype in SQL Server-CodePudding

I have a table in the SQL server database and it has a field with the type nvarchar. It stores the values in the format as shown below:

   ROW 1 - ["value1", "value2", "value3"]
   ROW 2 - ["value4","value5"]
   ROW 3 - ["value2", "value1"]
   ROW 4 - ["value3", "value6", "value7"]

I need to get select all the unique values from here. So I need to get the following values in the result.

Result
------
value1
value2
value3
value4
value5
value6
value7

I tried with the DISTINCT query, but it is failing. I am very new to SQL server and it would be very much helpful if someone can help me out here. Thanks

CodePudding user response：

Those values are valid JSON arrays so you should be able to use OPENJSON to extract the values:

SELECT value
FROM MyTable
CROSS APPLY OPENJSON(MyCol);

Example:

DECLARE @MyTable TABLE (MyCol NVARCHAR(MAX));
INSERT @MyTable (MyCol)
VALUES(N'["value1", "value2", "value3"]'),
      (N'["value4","value5"]'),
      (N'["value2", "value1"]'),
      (N'["value3", "value6", "value7"]');

SELECT value
FROM @MyTable
CROSS APPLY OPENJSON(MyCol);