How can I use the LIKE operator on a list of strings to compare?

Question

I have a query I need to run on almost 2000 strings where it would be very helpful to be able to do a list like you can with the "IN" operator but using the LIKE comparison operation.

For example I want to check to see if pet_name is like any of these (but not exact): barfy, max, whiskers, champ, big-D, Big D, Sally

Using like it wouldn't be case sensitive and it can also have an underscore instead of a dash. Or a space. It will be a huge pain in the ass to write a large series of OR operators. I am running this on MySQL 5.1.

In my particular case I am looking for file names where the differences are usually a dash or an underscore where the opposite would be.

Answer 1

For this task I would suggest making use of RegExp capabilities in MySQL like this:

select * from EMP where name RLIKE 'jo|ith|der';

This is case insensitive match and will save from multiple like / OR conditions.

Answer 2

You could do something like this -

SELECT FIND_IN_SET(
  'bigD',
   REPLACE(REPLACE('barfy,max,whiskers,champ,big-D,Big D,Sally', '-', ''), ' ', '')
  ) has_petname;
+-------------+
| has_petname |
+-------------+
|           5 |
+-------------+

It will give a non-zero value (>0) if there is a pet_name we are looking for.

But I'd suggest you to create a table petnames and use SOUNDS LIKE function to compare names, in this case 'bigD' will be equal to 'big-D', e.g.:

SELECT 'bigD' SOUNDS LIKE 'big-D';
+---------------------------+
| 'bigD'SOUNDS LIKE 'big-D' |
+---------------------------+
|                         1 |
+---------------------------+

Example:

CREATE TABLE petnames(name VARCHAR(40));
INSERT INTO petnames VALUES
  ('barfy'),('max'),('whiskers'),('champ'),('big-D'),('Big D'),('Sally');

SELECT name FROM petnames WHERE 'bigD' SOUNDS LIKE name;
+-------+
| name  |
+-------+
| big-D |
| Big D |
+-------+