SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
 Oct  8 2022 05:58:25 
 Copyright (C) 2022 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE data (
  id INT identity(1,1),
  parameters VARCHAR(4000)
)
INSERT INTO data (parameters)
  VALUES
  ('PARAM1 PARAM2 PARAM4 PARAM6'),
  ('PARAM1 PARAM2 PARAM3 PARAM5'),
  ('PARAM3 PARAM5 PARAM6 PARAM7'),
  ('PARAM1 PARAM3 PARAM4 PARAM6')
;
4 rows affected

SELECT d.id, ds.value
  FROM data d
  CROSS APPLY STRING_SPLIT(d.parameters, ' ') ds


id
value


1
PARAM1

1
PARAM2

1
PARAM4

1
PARAM6

2
PARAM1

2
PARAM2

2
PARAM3

2
PARAM5

3
PARAM3

3
PARAM5

3
PARAM6

3
PARAM7

4
PARAM1

4
PARAM3

4
PARAM4

4
PARAM6


WITH f AS (
  SELECT 'PARAM1 PARAM3 PARAM7' as findstr
),
fr AS (
  SELECT t.value AS param
    FROM f
    CROSS APPLY STRING_SPLIT(f.findstr, ' ') t
)
SELECT d.id, count(1) AS num_matches
  FROM data d
  CROSS JOIN fr
  WHERE charindex(fr.param, d.parameters) > 0
  GROUP BY id
  HAVING count(1) >= 2
;


id
num_matches


2
2

3
2

4
2


WITH f AS (
  SELECT 'PARAM1 PARAM3 PARAM7' as findstr
)
SELECT d.id, count(1) AS num_matches
  FROM data d
  CROSS APPLY STRING_SPLIT(d.parameters, ' ') ds
  JOIN f ON 1=1
  WHERE charindex(ds.value, f.findstr) > 0
  GROUP BY d.id
  HAVING count(1) >= 2
;


id
num_matches


2
2

3
2

4
2

id	value
1	PARAM1
1	PARAM2
1	PARAM4
1	PARAM6
2	PARAM1
2	PARAM2
2	PARAM3
2	PARAM5
3	PARAM3
3	PARAM5
3	PARAM6
3	PARAM7
4	PARAM1
4	PARAM3
4	PARAM4
4	PARAM6