Count occurrences of character in a string using MySQL

Question

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

superset: string: http://stackoverflow.com/questions/12344795/count-the-number-of-occurences-of-a-string-in-a-varchar-field — Ciro Santilli OurBigBook.com, Nov 24 '15 at 21:26

Woot4Moo · Accepted Answer · 2013-01-04T12:58:47.183

41

select *  
from table  
where  LENGTH(name) - LENGTH(REPLACE(name, 'a', '')) between 1 and 2

Updated to use between.

edited Jan 04 '13 at 12:58

answered Jan 04 '13 at 12:51

Woot4Moo

23,987
16
94
151

Example: baaa => b. LENGTH(b) <= 1. But original string contains more than 2 'a's. – Simon Jan 04 '13 at 12:53
@simon that is a completely different query than what I am using. Further why are you taking the length of `b`? – Woot4Moo Jan 04 '13 at 12:54
@Woot4Moo Why? You first replace all 'a's with ''. After that you retrieve the length of the string without 'a's. Am I getting this wrong? – Simon Jan 04 '13 at 12:56
@Simon you were correct, I missed the initial subtraction, good eye :) – Woot4Moo Jan 04 '13 at 12:59
@Woot4Moo Now it's nearly perfect :) it must be greater than zero. You can use the BETWEEN-Operator with the operands 1 and 2 – Simon Jan 04 '13 at 13:00
@Simon ah yes, now to think how to avoid invoke length(replace) twice – Woot4Moo Jan 04 '13 at 13:02
@Pavlos there we go, use of between – Woot4Moo Jan 04 '13 at 13:03
@Simon there we go that should suffice now. – Woot4Moo Jan 04 '13 at 13:04
@Simon I am not sure if this is better or worse than regex in the general case. – Woot4Moo Jan 04 '13 at 13:07
@Woot4Moo should be in O(LENGTH(word)), which is pretty acceptable. Anyhow, I'm not sure if Regex is always in O(LENGTH(word)). – Simon Jan 04 '13 at 13:13
I m using UTF8 so Length(α) = 2 so it work with between 2 and 4 – Pavlos Jan 04 '13 at 13:19
Brilliant. Love it. I'm gonna KILL in wordfeud! utf(ix): `between 1*length('α') and 2*length('α')` – T4NK3R Mar 01 '15 at 09:08

score 2 · Answer 2 · answered Jan 04 '13 at 12:57

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.

score -3 · Answer 3 · answered Jan 04 '13 at 15:33

-3

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
/

answered Jan 04 '13 at 15:33

Art

5,616
1
20
22

3

`REGEXP_COUNT` is Oracle (11g and later), not MySQL. – Xophmeister Jan 04 '13 at 16:45
Look here-MySQL 5.1 Reference Manual - Regular Expressions: http://dev.mysql.com/doc/refman/5.1/en/regexp.html – Art Jan 04 '13 at 19:07
2

There is no mention of `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: http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions135.htm – Xophmeister Jan 04 '13 at 20:09

Count occurrences of character in a string using MySQL

3 Answers3

Linked

Related