My google-fu is failing me, If anyone could point me in the right direction for concepts or terms would be great.
I'm trying to fetch rows from a mysql database. Lets say 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
would return D, DEF, ED, EDF, FED
What you need is a user defined function to check if the strings match. Here is one:
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 will get what you want:
select example_column from your_table where permutes(example_column ,'def');
What the function does is take all needle characters and see if they exist in the haystack. Each needle it is taken out from the haystack before the next needle is checked, so you won't get doubles.
instr(haystack into instr(binary haystack on line 7 if you want this to be case sensitiveI'm leaving this here, even though I realize that it doesn't solve the problem. It would pick up 'FEED'. The idea might help someone else.
You can do this using rlike or regex:
select *
from table t
where col rlike concat('[', 'DEF', ']+')
This is constructing the simple regular expression '[DEF]+', which is the pattern you seem to want.
EDIT:
If you break up the original string into characters, you can do it as:
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);
The first condition in the having clause checks that all the characters are there. The second that none appear more than once. Note that this will not work if there are duplicate letters for the comparison.
I concluded 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 the accurate query will be:
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?))$'
ABCDEFG?
{0,1} is just ?