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.

Solution 1

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 |
+-------+

Solution 2

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.

Solution 3

As first step put all static values in any temporary table, this would be lookup dictionary.

SELECT * FROM Table t 
WHERE EXISTS (
            SELECT * 
            FROM LookupTable l 
            WHERE t.PetName LIKE '%' + l.Value + '%'
            )

Comments

[sll]

Rigth, missed that (added now). I believe the main challenge for OP was processing multiple entries, I believe he know how to apply LIKE but anyway you was right

[Patrick]

I am not sure I follow on two accounts, first how to get it into the temp table (which I can look up) and second how this operation is working. Specifically I looks like in the subquery you are doing a comparison of a value from the calling query directly within that subquery. Is that even possible?

[JSuar]

Would the OP need to enter static values with a wildcard (eg big_d) to account for different values (big-D, Big D, etc) like the OP described? Also, LCASE() needed as well?

[JSuar]

@pthurmond, subquery is possible: dev.mysql.com/doc/refman/5.0/en/…

[Patrick]

Will this ignore the difference between a dash and an underscore?

[Patrick]

+1 for the interesting concept. But I don't think you follow what I am trying to accomplish (that or I am just not following your implementation). Basically I have files that have sister files they need to be associated with. But when the client generated a lot of these sister files (multiple people did this) they had some format changes automatically or manually happen. So "bobs-green-80-hat.eps" sometimes became "bobs-green_80-hat.eps.jpg" and other times it became "BOBS-GREEN-80-HAT.EPS.JPG", depending on who did it.

[Devart]

Do you store file names in the table?

[anubhava]

Sure you can run this query to get that behavior: select * from EMP where replace(name, '-', '_') RLIKE 'jo|ith|der';

[Patrick]

Well that is the tricky part. We had 2 terabytes of files that consisted of an eps, a psd, or a tif file that had a paired jpg file generated from it for presentation on a web page. Every file was created by the client and they had to send this hard drive with all the files on it around to different locations so that they could add their files. This created a lot of inconsistencies. To make matters worse, once we got the drive we needed to get the data pages up on the site for them quickly so we had to upload the jpgs to the server and then send the drive to our host to get the rest up.

[Patrick]

That way we could get the basic data pages setup and they could add tagging data while we came back later (once our host got the drive and hooked it up to the server for the direct transfer) and attach the eps or other formatted file to the uploaded pages based on the name. The problem is these inconsistencies meant that the jpgs and paired file were not always identically named. So I had to write a script that played a guessing game as to what was the correct file to attach. All in all this involved over 17,000 pairs of files (over 32,000 files in total).

[Patrick]

In the end there were about 1,700 files that I could not get identified. So I was trying to find out which nodes didn't have a matching pair.

[Patrick]

Like I said, this whole thing was a huge pain in the ass.

[Stephen Saucier]

This supremely useful. Copy a list of results from another query, change each line break to a pipe, and this works incredibly well.

[anubhava]

select replace('abc\nline', '\n', ':'); replaced newline with :