CODEWITHSUNDEEP
mysqlsqlstringselect
Muleskinner
Muleskinner

Reputation: 14468

In MySQL, how to get all strings containing at least two characters in predefined list

I have a table of strings and want to retrieve all rows which contain at least two characters of a predefined list. Better explained by example:

My table looks like this:

Id|Word
 1|Cat
 2|Chicken
 3|Dog
 4|Elephant
 5|Fish
 6|Goat

This could be my char list:

Characterlist = 'A', 'B', 'C', 'E'

The select statement should then return: Cat, Chicken, Elephant

I have looked through the string functions in mySQL docs but can not find a suitable function for doing this. Is it even possible?

Upvotes: 2

Views: 1986

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270599

Here's an inefficient way which relies on adding up the 0 or 1s returned by boolean conditions:

SELECT
  *,
  (
    (UPPER(Word) LIKE '%A%') +
    (UPPER(Word) LIKE '%B%') + 
    (UPPER(Word) LIKE '%C%') + 
    (UPPER(Word) LIKE '%D%') 
  ) AS lettermatches
FROM yourtable
HAVING lettermatches >= 2

The LIKE conditions each return a 0 or 1. Those are added together, and the HAVING clause limits the result to the rows scoring 2 or greater.

It might be faster to use LOCATE() instead of LIKE:

SELECT
  *,
  (
    (LOCATE('A', UPPER(Word)) > 0) +
    (LOCATE('B', UPPER(Word)) > 0) + 
    (LOCATE('C', UPPER(Word)) > 0) + 
    (LOCATE('D', UPPER(Word)) > 0) 
  ) AS lettermatches
FROM yourtable
HAVING lettermatches >= 2

Upvotes: 4

Related Questions