Cfrgtkky

I have the below table

Col1       Col2

1          ACA,ABS,PHR

2          PHR

3          MCM

4          ABC

Now I want to filter the data from this so if I have a filter parameter as say 'ABS,MCM' I want to get only the rows which have at least one matching code. So in this case I should get the filtered result as

Col1       Col2

1          ACA,ABS,PHR

2          MCM

Now I can use the query Select * from myTable where Col2 in ('ABS','MCM') but then it won't retrieve the first row ACA,ABS,PHR. Can some one please tell me how I can do a text search for codes so as long as one code matches in Col2 I get the row so I can pass in directly the , delimited list and as long as it finds a single match the row is retrieved.

Thanks

You could also split the string and then search on the splitted values.
An example could be by creating and using this function.

Starting from SQL Server 2016 you could use the built in STRING_SPLIT() function.

Creating the function from the blogpost and executing this query afterwards

SELECT DISTINCT Col1,Col2 

FROM dbo.Searching

CROSS APPLY dbo.fnSplitString(col2,',')

WHERE splitdata in ('ABS','MCM');

Result

Col1    Col2

1   ACA,ABS,PHR

3   MCM

DB<>Fiddle

An optimization could be proactively splitting the table into a permanent one so you don't need to do splitting on the entire table everytime. (If the values are static)

EDIT

Comment by @Erikdarling

There are better ways to split strings: Comparing string splitting /
concatenation methods, including a much better one at SSC by Jeff
Moden.

The function by Jeff Moden

After creating the function in his blogpost, you could use a query that resembles the previous one.

  SELECT Col1,Col2

  FROM dbo.Searching

  CROSS APPLY dbo.DelimitedSplit8k(Col2,',') split

  WHERE split.Item in ('ABS','MCM');

DB<>Fiddle

When I see filter parameter, I think input parameter to a stored procedure, so nothing hardcoded in the actual query. This is a twist on @Randi's version that uses an inline string splitter technique to split the incoming parameter into rows and using CROSS APPLY and LIKE to find matches.

--demo setup

declare @T table(col1 int, col2 varchar(100))

insert into @T(col1,col2) values

(1,'ACA,ABS,PHR'),

(2,'PHR'),

(3,'MCM'),

(4,'ABC')



--Declare the input parameter - passed into a stored procedure perhaps?

Declare @Parm varchar(100) = 'ABS,MCM'





--Common Table Expression - Inline string splitter, since you can't use STRING_SPLIT() from SQL 2016

;WITH SplitParms

AS (

    SELECT LTRIM(RTRIM(m.n.value('.[1]', 'varchar(8000)'))) AS Code

    FROM (

        SELECT CAST('<XMLRoot><RowData>' + REPLACE(@Parm, ',', '</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x

        ) t

    CROSS APPLY x.nodes('/XMLRoot/RowData') m(n)

    )

--use CROSS APPLY and LIKE to find matching rows

SELECT t.*

FROM @t t

CROSS APPLY SplitParms sp

WHERE t.col2 LIKE ('%' + sp.Code + '%')

| col1 | col2        |

|------|-------------|

| 1    | ACA,ABS,PHR |

| 3    | MCM         |

You would need to use like.
where col2 like '%ABS%' or col2 like '%MCM%'

Here is another solution. I don't have a large enough dataset to show performance gains either way, but one option (my Attempt 1) splits the string you are searching rather than the entire table. Of course, unbound string searches may be more costly than you want to pay, but you can do testing to show. Alternatively, in Attempt 3, I show how you can do it without using a string of OR clauses or string_split.

/** BUILD UP DATA */

DECLARE @SearchString VARCHAR(100) = 'ABS,MCM'



DECLARE @Test TABLE

    (

    Col1 INT NOT NULL PRIMARY KEY IDENTITY(1,1)

    , Col2 VARCHAR(100) NULL

    )



INSERT INTO @Test

(Col2)

VALUES ('ACA,ABS PHR')

    , ('PHR')

    , ('MCM')

    , ('ABC')

    , ('ABS,MCM')   --Included to demonstrate if one record has multiple code matches.



/** ATTEMPT 1 - UNBOUNDED WILDCARD SEARCH

    - Still uses STRING_SPLIT, just on the Search String rather than the main table.

    */

;WITH CTE_Distinct AS

    (

    SELECT T.*

        , RN = ROW_NUMBER() OVER (PARTITION BY T.Col1 ORDER BY T.Col1)

    FROM @Test AS T

        CROSS APPLY STRING_SPLIT(@SearchString, ',') AS V 

    WHERE T.Col2 LIKE '%' + LTRIM(RTRIM(V.value)) + '%'

    )

SELECT * FROM CTE_Distinct WHERE RN = 1



/** ATTEMPT 2 - DIRECT QUERY, 

    but uses STRING_SPLIT on the Col2

    */

;WITH CTE_Distinct AS

    (

    SELECT T.*

        , RN = ROW_NUMBER() OVER (PARTITION BY T.Col1 ORDER BY T.Col1)

    FROM @Test AS T

        CROSS APPLY STRING_SPLIT(T.Col2, ',') AS V 

    WHERE V.value IN ('ABS', 'MCM')

    )

SELECT * FROM CTE_Distinct WHERE RN = 1



/** ATTEMPT 3 - UNBOUND WILDCARD, but no string splitting required.  

    */

DECLARE @SearchTable TABLE

    (

    Code VARCHAR(3) NOT NULL

    )



INSERT INTO @SearchTable

(Code)

VALUES ('ABS')

    , ('MCM')



;WITH CTE_Distinct AS

    (

    SELECT T.*

        , RN = ROW_NUMBER() OVER (PARTITION BY T.Col1 ORDER BY T.Col1)

    FROM @Test AS T

        INNER JOIN @SearchTable AS P ON T.Col2 LIKE '%' + P.Code + '%'

    )

SELECT * FROM CTE_Distinct WHERE RN = 1

The most correct solution is to normalize. You need to put the codes in another table with a foreign key:

CREATE TABLE MyThings (

    Col1 IDENTITY PRIMARY KEY,

    -- Other columns you already have on the table

    Col3 VARCHAR(200) NOT NULL,

    Col4 DECIMAL NOT NULL

);



-- Probably needs some kind of primary key.

CREATE TABLE MyThingCodes (

    Col1 NOT NULL REFERENCES MyThings (Col1),

    Code VARCHAR(30) NOT NULL

);

And we can index it (since the codes are short) to speed up these queries:

CREATE INDEX idx_MyThingCodes_Code ON MyThingCodes (Code);

Then you can use an EXISTS query to find rows that match the code:

SELECT *

FROM MyThings

WHERE EXISTS (

    SELECT 1

    FROM MyThingCodes

    WHERE

        MyThingCodes.Col1 = MyThings.Col1

        AND MyThingCodes.Code IN ('ABS','MCM')

)