How to find a csv list of codes that can exist anywhere in a varchar(max)

Transact-SQL
1

Hi all,

I need to return rows if a column contains all of the codes from a comma separate list and the codes can be anywhere in the column.

Example: I have a parameter passed in with a value of 'N40,M300,A20' and in the varchar(max) column is something like "Remark N40. Error value A20, next update M300, resend G10". Since the column has all 3 codes in the list I need it returned. The passed in list can contain 1 to n values. Any ideas other than writing a bunch of parsing / looping logic?

Thanks

2

Something like this:


DROP TABLE IF EXISTS #main_table;
CREATE TABLE #main_table ( varchar_max_column varchar(max) NULL );
INSERT INTO #main_table VALUES('Remark N40. Error value A20, next update M300, resend G10'),
    ('N40 ... G10 ... M300')

DECLARE @parameter varchar(8000)
SET @parameter = 'N40,M300,A20'

;WITH cte_split_values AS (
    SELECT Value
    FROM STRING_SPLIT(@parameter, ',')
)
SELECT mt.*
FROM #main_table mt
WHERE (SELECT COUNT(*) FROM cte_split_values
    WHERE mt.varchar_max_column + '.' LIKE '%[^A-Z]' + value + '[^A-Z]%') = 
    (SELECT COUNT(*) FROM  cte_split_values)
3

Thanks Scott, you're the man! Now I just have to make sure all the customers are on 2016 or later :slight_smile:

4

You can replace the STRING_SPLIT function with dbo.DelimitedSplit8K for customers not yet on SQL 2016. The rest of the code won't change.