1

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.

  • Do you always want to break your words up into syllables two letters long? – Vérace Sep 4 '17 at 5:44
  • 1
    Is this Hawaiian or Japanese? Are there any 1-letter syllables or 3-or-more-letter syllables? (I see you have o as a syllable...) – Oreo Sep 4 '17 at 9:05
  • To the OP (and @OblivionCoder) : You can always comment in your own questions. But you need to first merge your accounts. It seems you posted the question from an unregistered account. See: dba.stackexchange.com/help/merging-accounts – ypercubeᵀᴹ Sep 4 '17 at 9:09
1

Try some REPLACE() tricks:

SELECT *
FROM #words
WHERE REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    word
    ,'ka','')
    ,'ke','')
    ,'ki','')
    ,'ko','')
    ,'ku','')
    = '';
0
WHERE word REGEXP "^(k[aeiou])+$"

will check for a pair of (k+vowel) letter pairs.

  • k matches just k.
  • [aeiou] matches any of the letters in the brackets.
  • ^ and $ "anchor" both ends -- no other text on either end
  • ()+ says "one or more"
  • The query gets kakeru. – Oblivion Coder Sep 5 '17 at 4:50
  • @OblivionCoder -- sometimes I am blind to the question. See if the changes I just made match the requirements. – Rick James Sep 5 '17 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.