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
LVL 1
trevor1940Asked:
Who is Participating?
 
MishaConnect With a Mentor ProgrammerCommented:
This question have already answer. Look this article:
https://stackoverflow.com/questions/1865353/combining-like-and-in-for-sql-server
0
 
ste5anSenior DeveloperCommented:
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

0
 
trevor1940Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.