MySQL Difficult Where Clause

Question

I need help with a difficult query which I may not explain well with words.

The query needs to only return results where all the characters in the code column are in the where clause.

Say I had the following table and wanted to return the code and position where ABC.

Table:

code	position
ABC	100
ABCD	200
ABCDE	300
CBA	400
BCA	500
A	600
BC	700
KABC	800
CABD	900
CA	1000

Expected Results:

code	position
ABC	100
CBA	400
BCA	500
A	600
BC	700
CA	1000

I have tried many variations of like with both % and _ operator’s. Beginning to think MySQL doesn’t have this functionality. Any ideas? I’m at the end of my rope.

Answer 1

A different approach to @Barbaros Özhan (which I like a lot) is by using REGEXP, like so:

SELECT
  *
FROM test
WHERE `code` REGEXP '[^ABC]' = 0;

DBFiddle

This is basically filtering out every [code] which contains any other character than 'ABC'. This is case-sensitive though, but you can add options to the regexp to make it case-insensitive (and many other options).

Check the docs

Answer 2

You can use concurrent REPLACE() functions in order to pick the letters A,B,C as been reduced from the values of the code column, and then apply LENGTH() function such as

SELECT *
  FROM t
 WHERE LENGTH(REPLACE(REPLACE(REPLACE(code,'A',''),'B',''),'C',''))=0

Demo