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 |