Search 'like' multiple times in many conditions with dynamic JSON array input

Question

I want a query in SQL Server, not in JavaScript. I have a table [dbo].[tbl_Sample1] with these columns:

[ID] [int] IDENTITY(1,1) NOT NULL,
[Content1] [nvarchar](4000) NOT NULL,
[Content2] [nvarchar](4000) NULL
   

(with many rows have content1, content2) and input like this:

declare @tagJsonArray nvarchar(4000)
-- set @tagJsonArray = '["US", "UK", "FR"]' ... (dynamic json array)
declare @keywordJsonArray nvarchar(4000)
-- set @keywordJsonArray = '["ing","ied","ed"]' ... (dynamic json array)

How can I write a query to select like this:

SELECT * 
FROM [dbo].[tbl_Sample1]
WHERE -- Content1 contains any in @tagJsonArray (search Like)
 -- AND Content2 contains any in @keywordJsonArray (search Like)

Thank you. -- Edit: thank you AlwaysLearning It's '["US", "UK", "FR"]', not '["US, UK, FR"]'. Sorry about that.

Answer 1

This can be accomplished with a function. I have this function called fn_SplitStrings_Moden that I found years ago. I use it in almost all my databases in some form. It takes a delimited string and breaks it into a table. As @AlwaysLeaning says, the tag list is not formatted properly. I used exactly what you provided. If you meant that each element will be a string, then you can modify the below code to be more like the keyword split.

Here's a sample code which demonstrates what you asked for:

--This function will split delimited strings. Returns a table with each row containg one of the split strings.
CREATE FUNCTION [dbo].[fn_SplitStrings_Moden]
(
   @List NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
FROM cteStart s;

--Table variable for demo content.
DECLARE @content TABLE (
    content1 nvarchar(50)
    , content2 nvarchar(50)
);

--Load some demo content into variable.
INSERT INTO @content (content1, content2)
VALUES 
    ('US', 'Sleeping')
    , ('US', 'Packs')
    , ('UK', 'Fried')
    , ('UK', 'Sleeps')
;

--Demo JSON arrays.
DECLARE @tagJsonArray nvarchar(4000) = '["US, UK, FR"]';  --Badly formatted array?
DECLARE @keywordJsonArray nvarchar(4000) = '["ing","ied","ed"]';

--Cleanup tag array to remove JSON encapsulation.
SET @tagJsonArray = STUFF(LEFT(@tagJsonArray, LEN(@tagJsonArray) - 2),1,2,'');

--Cleanup keyword array to remove JSON encapsulation.
SET @keywordJsonArray = STUFF(LEFT(@keywordJsonArray, LEN(@keywordJsonArray)-1),1,1,'');

--Demo of results of split function.
SELECT
    t.Item
    , RTRIM(LTRIM(t.Item)) as mod_item
FROM fn_SplitStrings_Moden(@tagJsonArray,',') as t;

--Demo of results of split function
SELECT
    k.Item
    , REPLACE(k.Item,'"','') as mod_item
FROM fn_SplitStrings_Moden(@keywordJsonArray,',') as k;

--Final Query
SELECT DISTINCT
    c.*
    --, RTRIM(LTRIM(t.Item)) as tagMatch
    --, REPLACE(k.Item,'"','') as keywordMatch
FROM @content as c
    LEFT OUTER JOIN fn_SplitStrings_Moden(@tagJsonArray,',') as t
        ON c.content1 like '%' + RTRIM(LTRIM(t.Item)) + '%'
    LEFT OUTER JOIN fn_SplitStrings_Moden(@keywordJsonArray, ',') as k
        ON c.content2 like '%' + REPLACE(k.item,'"','') + '%'
WHERE t.Item IS NOT NULL
    AND k.Item IS NOT NULL

Results: