Example Words: a, akkka, akokaa, kokoko, kakao, oooaooa, kkako, kakaoa
I need the regexp witch gives words with 2 or less 'a' but not the words without 'a'
Result: a, akka, kakao, oooaooa, kkako
Ok actually I am using:
SELECT word FROM dictionary_gr WHERE word REGEXP 'λ{2,3}' LIMIT 0 , 30
this returns 0 lines there are words with 2 λ's and 3 λ's
select *
from table
where LENGTH(name) - LENGTH(REPLACE(name, 'a', '')) between 1 and 2
Updated to use between.
b?I don't know what MySQL supports in terms of lookaround assertions, but the following will do the trick:
^(?=.*a.*a?.*)(?!.*a.*a.*a.*).*$
We have a lookahead assertion that matches 1 or 2 a characters in the string. Then we have a negative lookahead that disregards 3 or more as anywhere in the string. Then the final pattern just matches the whole string, providing the first two assertions are satisfied.
If MySQL doesn't support lookarounds, then @Woot4Moo's answer would be the way to go.
Quick and dirty:
Select word, number_of_as From
(
Select 'akkka' word, REGEXP_COUNT('akkka', 'a') number_of_as From dual
)
Where number_of_as <= 2
/
REGEXP_COUNT is Oracle (11g and later), not MySQL.
REGEXP_COUNT in the MySQL manual (the page you linked), nor any function like it. As I say, it's an Oracle function that was introduced in 11g: docs.oracle.com/cd/B28359_01/server.111/b28286/functions135.htm