regexp_count seems to not work as it should

[Rajasql]

The below SQL's should give different answers as regexp rules state that * is a special character which needs to be escaped with \ to be considered as a literal string. This second should literally match for A*B and return 2 but it is also taking AB as a match and returning 3

SELECT regexp_count('nA*BsABreA*Bthe', 'A*B') str_cnt;

SELECT regexp_count('nA*BsABreA*Bthe', 'A\*B') str_cnt;

[Walter_C]

Is this being executed on SQL editor or on a Notebook?

[Rajasql]

It is being executed on a notebook.

[PabloCSD · Accepted solution]

Hello @Rajasql ,

Try this way (it worked for me in a Databricks notebook and it returns 2):

SELECT regexp_count('nA*BsABreA*Bthe', 'A\\*B') str_cnt;

[Rajasql]

It fails if this is the code and returns 3 which it should not.

SELECT regexp_count('nA*BsA\*BreA*Bthe', 'A\\*B') str_cnt;

This is what I am following for Regexp standards

https://www.regular-expressions.info/quickstart.html