Where clause from array column

I have a column with value ["NONCODE","OFFCR", "SPVR", "CORD"] inside.

I want to return a value when one of the values ​​in the column matches.

Select * from table where column='SPVR' --will be true or return the values
Select * from table where column='MGR' --will be false or return an empty values

In that field column, I have value

["NONCODE","OFFCR", "SPVR", "CORD"]
["NONCODE","OFFCR", "SPVR", "CORD"]
["ASST-MGR"]
["ASST-MGR"]
["MGR"]
["MGR"]

If I query

Select * from table where column='SPVR'

the result shows the only row contain ‘SPVR’

Select * from table where column='MGR'

the result shows the only row contain ‘MGR’ (the row with ‘ASST-MGR’ do not appear)

If I use the ‘LIKE’ command, all the rows containing MGR will appear

Answer

Original answer:

If you use SQL Server 2016+, you may try to parse this data using OPENJSON() function, because ["NONCODE","OFFCR", "SPVR", "CORD"] is a valid JSON array. OPENJSON() is a table-valued function that parses JSON text, so you can use any set-based approach to get your expected results.

Table:

CREATE TABLE #Data(
   [Text] varchar(100)
) 
INSERT INTO #Data
   ([Text]) 
VALUES
   ('["NONCODE","OFFCR", "SPVR", "CORD"]'),
   ('["NONCODE","OFFCR", "SPVR", "CORD"]'),
   ('["ASST-MGR"]'),
   ('["ASST-MGR"]'),
   ('["MGR"]'),
   ('["MGR"]')

Statement:

SELECT d.*
FROM #Data d
CROSS APPLY OPENJSON(d.[Text]) j
WHERE j.[value] = 'SPVR'
-- or, if needed:
-- WHERE j.[value] LIKE '%MGR%' 

Output:

-----------------------------------
Text
-----------------------------------
["NONCODE","OFFCR", "SPVR", "CORD"]
["NONCODE","OFFCR", "SPVR", "CORD"]

Update:

For SQL Server 2012, you may try to split the data using XML:

SELECT 
   t.[Text]
FROM (
   SELECT 
      [Text],
      CAST(CONCAT('<x>', REPLACE(REPLACE(REPLACE(REPLACE([Text], '[', ''), ']', ''), ' ', ''), ',', '</x><x>'), '</x>') AS xml) AS XMLText
   FROM #Data
) t
CROSS APPLY (
   SELECT N.value('.', 'varchar(100)') AS [value] 
   FROM t.XMLText.nodes('x') AS x(N)
) c
WHERE c.[value] = '"SPVR"'