Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Count occurrences of character in a string using MySQL

Tags:

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

like image 594
Pavlos Avatar asked Jan 04 '13 12:01

Pavlos


People also ask

How do you count occurrences of a character in a string in MySQL?

MySQL CHAR_LENGTH() returns the length (how many characters are there) of a given string. The function simply counts the number characters and ignore whether the character(s) are single-byte or multi-byte.

How do you count occurrences of a character in a string?

First, we split the string by spaces in a. Then, take a variable count = 0 and in every true condition we increment the count by 1. Now run a loop at 0 to length of string and check if our string is equal to the word.

How do I count the number of words in a string in MySQL?

To identify the number of words, we need to count the number of characters in a string and count the characters without the spaces and new lines. When we subtract the two, we get the word count. Let's look at an example. The query will return the number of words in each content row as wordcount .


2 Answers

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

Updated to use between.

like image 133
Woot4Moo Avatar answered Sep 19 '22 15:09

Woot4Moo


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.

like image 39
Xophmeister Avatar answered Sep 19 '22 15:09

Xophmeister