4

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

Ctrl Alt Design
  • 707
  • 3
  • 14

3 Answers3

3

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.

nl-x
  • 11,762
  • 7
  • 33
  • 61
  • change `instr(haystack` into `instr(binary haystack` on line 7 if you want this to be case sensitive – nl-x Apr 22 '14 at 15:11
  • forgive my ignorance, but what is that language is that function? – Ctrl Alt Design Apr 22 '14 at 16:04
  • It's called structured query language, aka sql ;) . Thought I don't know if all of it is standard or mysql specific sql. You can just put this in workbench or phpmyadmin. Creating user defined functions are common though in all known db's. – nl-x Apr 22 '14 at 17:01
  • HA! excellent, just added the function to mysql, worked like a charm. – Ctrl Alt Design Apr 22 '14 at 17:48
  • This works groovy but removes the ability to do wildcard searches with "_", I play around with it and see if I can figure it out. – Ctrl Alt Design Apr 24 '14 at 01:29
0

I'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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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?))$'

DEMO

dlyaza
  • 238
  • 2
  • 9