Combine multiple OR and LIKE mySql

Question

I understand that LIKE can not be used in inside IN clause from here.

My table has col1, col2, col3 columns.

How can I find multiple values in multiple columns using LIKE operator. Currently my query is:

SELECT col1, col2, col3
FROM table_name
WHERE
(   
    Col1 = '38' 
    OR Col1 LIKE '%,38'
    OR Col1 LIKE '%,38,%' 
    OR Col1 LIKE '38,%'
)
OR
(
    col2 = '38' 
    OR col2 LIKE '%,38'
    OR col2 LIKE '%,38,%' 
    OR col2 LIKE '38,%'
)
OR
(
    col3 = '38' 
    OR col3 LIKE '%,38'
    OR col3 LIKE '%,38,%' 
    OR col3 LIKE '38,%'
)

Is there a way make it smarter/shorter/faster? Thanks.!

Yes. Correct your data and stop storing multiple values in the same column, and then you don't have to work so hard to parse it back out of the comma-separated values. The problem with foolish decisions to not spend time to properly define your tables in the first place is that you end up working 100 times harder to retrieve the data when you need it. Then it becomes `WHERE (col1 = '38' or col2 = '38' or col3 = '38')`. — Ken White, Dec 08 '17 at 13:45
I agree with you, table should be normalised. But for now I have to stay with this legacy format. — , Dec 08 '17 at 14:10

score 3 · Accepted Answer · answered Dec 08 '17 at 13:50

You can use the function FIND_IN_SET(). MySQL has a dedicated function FIND_IN_SET() that returns field index if the value is found in a string containing comma-separated values.

Example

DROP TABLE table_name;

CREATE TABLE table_name
(col1 VARCHAR(100));

INSERT INTO table_name VALUES ('38');
INSERT INTO table_name VALUES ('37,38,39');
INSERT INTO table_name VALUES ('37,38');
INSERT INTO table_name VALUES ('38,39');
INSERT INTO table_name VALUES ('48');


SELECT col1
FROM table_name
WHERE FIND_IN_SET(38, col1) > 0;

Your solution would be

SELECT col1, col2, col3
FROM table_name
WHERE FIND_IN_SET(38, col1) > 0 
  OR FIND_IN_SET(38, col2) > 0 
  OR FIND_IN_SET(38, col3) > 0 ;

Combine multiple OR and LIKE mySql

1 Answers1