CODEWITHSUNDEEP
mysql
james
james

Reputation: 1041

MYSQL - search field for unordered text

I'm having problems trying to query the following data:

id. | name | type

 1.    bob | a 
 2.    sam | ab
 3.    jim | abc
 4.  sarah | ad
 5.   john | a
 6.    eve | bca
 7.  nikki | ca

I'm trying to write a MySQL query based on the following input:

a,
ac,
b*,
ac*

which I want to return the following names:

bob
john
sam
jim
eve
nikki

Breakdown: 

a  = bob, john
ac = nikki  
b* = sam, jim, eve
ac*= jim, eve, nikki

Explained:

a  = find results that are JUST a
ac = find results that contain ONLY an a AND c
b* = find results which contains a b
ac*  find results that contain at least an a and a c

As can be seen the type column can contain type info in different orders (not always increasing).

I'm sure this could be done by splitting the type column into type_a, type_b etc etc and then just have a bool in each one. But this could go up to z and I don't want 26 extra cols on my table!

is this possible to do in a single query, if so any help would be appreciated!

Sorry about the title I had no idea what to call it

Upvotes: 0

Views: 182

Answers (2)

eggyal
eggyal

Reputation: 125925

You can use regular expressions:

SELECT name FROM your_table WHERE
     type RLIKE '^a+$'                            -- only 'a'
  OR type RLIKE '^((a[ac]*c)|(c[ac]*a))[ac]*$'    -- only & both 'a' AND 'c'
  OR type RLIKE 'b'                               -- at least one 'b'
  OR type RLIKE '(a.*c)|(c.*a)'                   -- at least 'a' and 'c'
;

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425593

-- ac*, ac, a, b*

SELECT  *
FROM    mytable
WHERE   (
        type RLIKE 'a'
        AND type RLIKE 'c'
        )
        OR
        (
        type RLIKE 'a'
        AND type RLIKE 'c'
        AND NOT type RLIKE '[^ac]'
        )
        OR
        (
        type RLIKE 'a'
        AND NOT type RLIKE '[^a]'
        )
        OR
        (
        type RLIKE 'b'
        );

This won't use any indexes though.

If your table is MyISAM, you can store types like this:

id   name     type
7    nikki    a c

(note the spaces) and use FULLTEXT functionality:

CREATE FULLTEXT INDEX fx_mytable_type ON mytable (type);

-- ac*
SELECT  *
FROM    mytable
WHERE   MATCH(type) AGAINST '+"a" + "c"' IN BOOLEAN MODE);


-- ac
SELECT  *
FROM    mytable
WHERE   MATCH(type) AGAINST '+"a" + "c"' IN BOOLEAN MODE);
        AND NOT TYPE RLIKE '[^ ac]' -- note the space

Set @@ft_min_word_len = 1 for this to work.

Upvotes: 1

Related Questions