Link to home
Create AccountLog in
Avatar of trevor1940
trevor1940

asked on

SQL: Can you combine LIKE and IN within a single query?

Hi

Can you combine LIKE and IN within a single MYSQL query?

I have a list of codes I need to search for something like this

select * FROM mytable WHERE codes LIKE '%ABC%' OR code LIKE '%XYZ%';

Open in new window


The problem is the column codes  contains a coma separated list eg "ABC,EFG,XYZ"  so a standard IN clause wont work
ASKER CERTIFIED SOLUTION
Avatar of Misha
Misha
Flag of Russian Federation image
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of ste5an
ste5an
Flag of Germany image
Well, normalization is your friend. Otherwise you need a string split function. Using the built-in (SQL Server 2016+):

DECLARE @Unnormalized TABLE
    (
        ID INT IDENTITY ,
        Payload NVARCHAR(255) NOT NULL
    );

INSERT INTO @Unnormalized ( Payload )
VALUES ( N'abc,efg,xyz' ) ,
       ( N'abc,xyz' ) ,
       ( N'efg,xyz' ) ,
       ( N'xyz' ) ,
       ( N'efg' ) ,
       ( N'abc' );

SELECT *
FROM   @Unnormalized U
WHERE  EXISTS (   SELECT *
                  FROM   STRING_SPLIT(U.Payload, ',') SS
                  WHERE  SS.value IN ( 'abc', 'xyz' ));

Open in new window

Avatar of trevor1940
trevor1940

ASKER

I went with first comment because it is simpler to code via external perl script
Also The second comment mentions  Using the built-in (SQL Server 2016+) unsure if MYSQL has this function