MySQL query or regular expression only finds pattern D, E, F

Question

My google-fu fails if someone can point me in the right direction so that concepts or terms are good.

I am trying to get rows from mysql database. Suppose I have a variable equal to 'DEF' . I want to search the DB for records containing only DEF in any order.

example column

    ABC
    BC
    D
    DEX
    DEF
    DEFF
    ED
    EDF
    FED
    FEED

will return D, DEF, ED, EDF, FED

Answer 1

You need a custom function to check for matching strings. Here is one of them:

delimiter //
create function permutes(needles varchar(255), haystack varchar(255)) returns bool
begin
    declare needles_position, first_occurance int;
    set needles_position = length(needles);
    while needles_position > 0 do
        set first_occurance = instr(haystack, substring(needles, needles_position, 1));
        if first_occurance = 0 then
            return false;
        end if;
        set haystack = (select concat(substring(haystack,1,first_occurance-1), substring(haystack,first_occurance+1)));
        set needles_position = needles_position - 1;
    end while;
    return true;
end//

now you get what you want:

select example_column from your_table where permutes(example_column ,'def');

, , . , , .

Answer 2

, , . 'FEED'. - .

rlike regex:

select *
from table t
where col rlike concat('[', 'DEF', ']+')

'[DEF]+', , .

EDIT:

, :

select t.col
from table t left outer join
     (select 'D' as c union all select 'E' union all select 'F'
     ) c
     on t.col like concat('%', c.c, '%')
group by t.col
having sum(c.c is null) = 0 and 
       sum(length(t.col) - length(replace(t.col, c.c, '')) > 1);

having . -, . , , .

Answer 3

I made the following possibilities:

DEF
DFE
EDF
EFD
FDE
FED

REGEX (DEF|DFE|EDF|EFD|FDE|FED)


DF
DE
EF
ED
FE
FD

REGEX (DF|DE|EF|ED|FE|FD)

D
E
F

REGEX (D|E|F)

The exact query will look like this:

WHERE
COLUMN_NAME REGEXP '^(DEF|DFE|EDF|EFD|FDE|FED)$' OR 
COLUMN_NAME REGEXP '^(DF|DE|EF|ED|FE|FD)$' OR
COLUMN_NAME REGEXP '^(D|E|F)$'

OR

COLUMN_NAME 
REGEXP '^(DE{0,1}F{0,1}|DF{0,1}E{0,1}|ED{0,1}F{0,1}|
          EF{0,1}D{0,1}|FD{0,1}E{0,1}|FE{0,1}D{0,1})$'

Shortest:

REGEXP '^(D(E?F?|F?E?)|E(D?F?|F?D?)|F(D?E?|E?D?))$'

Demo