MySQL Difficult Where Clause

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:

Expected Results:

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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

Method 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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0