CODEWITHSUNDEEP
sqlsql-server
Fajar Taufik
Fajar Taufik

Reputation: 43

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

Upvotes: 2

Views: 5357

Answers (5)

Spencer
Spencer

Reputation: 67

Not sure what database or SQL flavor you're using but array_contains works for querying an array of strings:

SELECT *
FROM table
WHERE ARRAY_CONTAINS(column_name, "string") = True;

Doing it this way generate the actual rows of data, not just a Boolean as shown in the documentation.

https://docs.data.world/documentation/sql/reference/functions/array_contains.html

Upvotes: 0

Zhorov
Zhorov

Reputation: 29943

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"'

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37367

Alternatively to given answer, I'd suggest charindex in this scenario:

SELECT array_col
FROM yourTable
WHERE CHARINDEX("SPVR", array_col) > 0

Upvotes: 0

Dharmendar Kumar &#39;DK&#39;
Dharmendar Kumar &#39;DK&#39;

Reputation: 2102

You can use the new string_split() tsql function to split your csv values into individual values. This was introduced in SQL 2016 (compatibility level 130). Eg below:

CREATE TABLE #myTable(vals varchar(100)) 
INSERT INTO #myTable(vals) 
VALUES('"NONCODE","OFFCR","SPVR","CORD"'), ('"NON","CODE", "PACE", "RACE"')

SELECT *
FROM #myTable CROSS APPLY string_split(vals, ',')
WHERE value = '"SPVR"'

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

While your current structure is not ideal and you should avoid storing CSV data into regular SQL columns, we can try the following workaround:

SELECT array_col
FROM yourTable
WHERE array_col LIKE '%' + '"SPVR"' + '%';

Demo

Note: SQL Server (at least currently) does not support a native array type. One workaround would be to use temporary tables or table-valued parameters.

Upvotes: 4

Related Questions