-1

Consider the following line to search for strings using the LIKE operator:

LIKE '%a%a%'

This returns when the selected string has two or more instances of the letter 'a' in the string. How can I use REGEXP operator to only match a string with two instances of the letter a, no more and no less?

I have tried to read the documentation but I can't wrap my head around the descriptions for the symbols used in regular expressions

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
Espen Sales
  • 99
  • 2
  • How about `.*a.*a.*`? – CinCout Jul 24 '19 at 18:27
  • 2
    Yeah I just tried that! Worked like a charm. But what I wanted that I didn't specify in my question was that I wanted only two intances of the letter a, no more and no less. so I wrote ```^[^a]*a[^a]*a[^a]$``` – Espen Sales Jul 24 '19 at 18:36
  • 1
    You're a natural! – CinCout Jul 24 '19 at 18:40
  • Why not simply `WHERE (LENGTH() - LENGTH(REPLACE(, 'a', ''))) >= 2` -> [demo](https://www.db-fiddle.com/f/fRLXimFJ18HCabeJiA8vTU/0) ... But mine suggestion and regex are both bad for indexes as they not really can be indexed unless you use a generated column – Raymond Nijland Jul 24 '19 at 18:42
  • 2
    looks like there's a `*` missing right before the `$` in the regexp. unless we intend to be checking for exactly one character following the second `'a'`. for exactly two 'a' characters anywhere in the string, `REGEXP '^[^a]*a[^a]*a[^a]*$'` – spencer7593 Jul 24 '19 at 18:52

1 Answers1

0

Try this:

^([^a]*a){2}[^a]*$

Here is Demo