MySQL – How to Get Rows Containing Only Certain Strings

MySQL

I have these words in my words table in a MySQL db:

kaki

koku

kao

kakeru

How can I get all rows that contains ONLY 'ka','ki','ku','ke','ko'?

I want to get kaki and koku but not kao and kakeru, because although kao contains ka, it also has o, and although kakeru contains ka and ke it also contains ru.

I have tried:

select *
from words
where (
    word like '%ka%'
    or word like '%ki%'
    or word like '%ku%'
    or word like '%ke%'
    or word like '%ko%'
    );

but I don't get the result that I want.

EDIT: I can't comment, but yes I want to break it into 2 syllables.

Best Answer

Try some REPLACE() tricks:

SELECT *
FROM #words
WHERE REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    word
    ,'ka','')
    ,'ke','')
    ,'ki','')
    ,'ko','')
    ,'ku','')
    = '';

Related Solutions

Mysql – How to get rows where count() is null

OK, without any detailed info about the tables' relationships (1-1, 1-to-many, many-to-many) and the primary keys, it's not easy to deal with this. But lets try.

The FROM clause has a 6-table join:

FROM 
  Bac              b0_  INNER JOIN 
  Bachelier        b4_   ON b0_.bachelier_cne = b4_.cne INNER JOIN 
  EtablissementBac e5_   ON b0_.etabBac_id = e5_.codeLieu INNER JOIN 
  Delegation       d1_   ON e5_.delegation_id = d1_.codeLieu INNER JOIN 
  MentionBac       m2_   ON b0_.mentionBac_id = m2_.codeMention INNER JOIN 
  TypeBac          t3_   ON b0_.typeBac_id = t3_.codeType

Since 2 tables (m2_ and t3_) are involved in the WHERE clause:

WHERE m2_.codeMention IN ('TB',  'B')
  AND t3_.codeType IN ('114',  '129')

and one more (d1_) in the GROUP BY clause:

GROUP BY 
  d1_.codeLieu, 
  m2_.codeMention, m2_.libelleMention, 
  t3_.codeType, t3_.libelleType, t3_.abbrType

we'll assume that these 3 should be join with proper INNER joins.

And since you are counting on b0_, this will probably have to be "LEFT" joined. That leaves with some choices about the other two tables (b4_ and e5_). So, lets rewrite the FROM part. First the 3 (sure JOIN) tables, then the ambiguous 2 and lastly the sure (LEFT JOIN) b0_:

FROM 
  TypeBac          t3_  INNER JOIN 
  MentionBac       m2_  INNER JOIN 
  Delegation       d1_  INNER JOIN 

  EtablissementBac e5_   ON e5_.delegation_id = d1_.codeLieu INNER JOIN 
  Bachelier        b4_  
                        LEFT JOIN
  Bac              b0_   ON b0_.bachelier_cne = b4_.cne 
                        AND b0_.etabBac_id = e5_.codeLieu 
                        AND b0_.typeBac_id = t3_.codeType
                        AND b0_.mentionBac_id = m2_.codeMention

Well, that doesn't look correct. Because (except for the d1 - e5 join), we don't have a way to join the other 4 tables. All 4 are to be joined with the b4 which has to be last (as we want to LEFT JOIN it.) This observation leaves with one choice:

FROM 
  TypeBac          t3_  CROSS JOIN 
  MentionBac       m2_  CROSS JOIN 
  Delegation       d1_  INNER JOIN 

  EtablissementBac e5_   ON e5_.delegation_id = d1_.codeLieu 
                        CROSS JOIN 
  Bachelier        b4_  
                        LEFT JOIN
  Bac              b0_   ON b0_.bachelier_cne = b4_.cne 
                        AND b0_.etabBac_id = e5_.codeLieu 
                        AND b0_.typeBac_id = t3_.codeType
                        AND b0_.mentionBac_id = m2_.codeMention

Try changing the FROM clause as above. The only other option I see is the removal of the CROSS JOIN Bachelier b4_, as no column of this table is included in the SELECT.

After more comments and a request, here is one final trial:

SELECT COUNT( b0_.id ) AS sclr0, 
       d1_.codeLieu AS codeLieu1, d1_.nomLieu AS nomLieu2, 
         d1_.lngLieu AS lngLieu3, d1_.latLieu AS latLieu4, 
       m2_.libelleMention AS libelleMention5, 
       t3_.libelleType AS libelleType6
FROM 
  TypeBac          t3_  
    CROSS JOIN 
  MentionBac       m2_  
    CROSS JOIN 
  Delegation       d1_  
    INNER JOIN 
  EtablissementBac e5_   ON e5_.delegation_id = d1_.codeLieu 
    LEFT JOIN
  Bac              b0_   ON b0_.etabBac_id = e5_.codeLieu 
                        AND b0_.typeBac_id = t3_.codeType
                        AND b0_.mentionBac_id = m2_.codeMention 
WHERE m2_.codeMention IN ('TB',  'B')
  AND t3_.codeType IN ('114',  '129')
  AND EXISTS
        ( SELECT *
          FROM Bac b
          WHERE b.etabBac_id = e5_.codeLieu 
            AND b.typeBac_id = t3_.codeType
            AND b.mentionBac_id = m2_.codeMention 
        )
GROUP BY 
    d1_.codeLieu, 
    m2_.codeMention, m2_.libelleMention, 
    t3_.codeType, t3_.libelleType, t3_.abbrType ;

MySQL – How to Handle Stop Words in Queries

I state you have two possibilities with MySQL:

Deactivate stop word list

You can deactivate the stop word list completely.

To override the default stopword list, set the ft_stopword_file system variable. [...] The variable value should be the path name of the file containing the stopword list, or the empty string to disable stopword filtering.

http://dev.mysql.com/doc/refman/5.5/en/fulltext-fine-tuning.html

(You might want to change ft_min_word_len too, since its default value is 4 and "go" is only 2 characters long.)

This would allow you to use the fulltext index even in case of presence of stop words, which offers by far more possibilities than any search with LIKE and is a lot faster.

Control the query

Your application can scan the search words before the query to actually change the query depending on "do the search words contain a stop word". If yes, query with LIKE, otherwise query with AGAINST. This way still leaves you with a very slow LIKE based query in the stop word case!

I do not know of further possibilities with MySQL as its capabilities concerning fulltext searches are rather limited.

Non-MySQL-Options (Take a look around)

There is other software around, which eases problems of search&find, for example the modified MySQL code Sphinx. Sphinx obviously has a different approach than MySQL since its default stop word list is empty. If there even is the possibility to add another software to your infrastructure, you might take a look at its possibilities or search for other software which might fulfill your needs (e.g. Apache Lucene).