Question 175296 in Mysql Question posted in
The official MySQL documentation can be found here.

Can I SELECT words with specific characters in a MySQL database?

I want to write a SELECT query in MySQL to find words with only and only specific characters in database.
For example if data is :

id  word
---------
1    a
2    b
3    aa
4    ab
5    aaa
6    aba
7    abc
8    abcd
9    abac
10   abaca

The query result to find ‘a’ and ‘b’ should be :

a
b
ab

And the query result to find ‘a’ and ‘b’ and ‘a’ (Repeat ‘a’ twice) should be :

a
b
aa
ab
aba

And the query result to find ‘a’ and ‘b’ and ‘a’ and ‘a’ (Repeat ‘a’ three times) should be :

a
b
aa
ab
aaa
aba

Any idea ?

SELECT * FROM `WORDS_table` WHERE `word` LIKE '%a%' AND `word` LIKE '%b%'

is not correct, because it returns all the words with any of selected characters.

NOTE : I continue to use php language to process the query result.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks for the quick reply and helpful tutorial The query passed most of the tests successfully, but this query does not consider duplicate letters! I updated the question.


  2. 0

    You could use a regex:

    select * from words where word rlike '^[ab]+$'

    Regexp breakdown:

    ^       beginning of the string
[ab]+   1 to N occurrences of character 'a' or 'b' 
$       end of the string

    fiddle:

    select * 
from ( values row('a'), row('ab'), row('abc'), row('abcd') ) w(word)
where word rlike '^[ab]+$'
    word
    a
    ab
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search