Get unique values from nvarchar datatype in SQL Server

Question

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

squillman · Accepted Answer · 2022-05-09 13:47:19Z

1

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);

answered May 9, 2022 at 13:47

squillman

13.7k3 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get unique values from nvarchar datatype in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related