topic regexp_count seems to not work as it should in Administration & Architecture

regexp_count seems to not work as it should

Rajasql — Wed, 08 Jan 2025 19:41:50 GMT

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;

Re: regexp_count seems to not work as it should

Walter_C — Wed, 08 Jan 2025 20:09:17 GMT

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

Re: regexp_count seems to not work as it should

PabloCSD — Wed, 08 Jan 2025 20:17:15 GMT

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;

Re: regexp_count seems to not work as it should

Rajasql — Wed, 08 Jan 2025 20:20:13 GMT

It is being executed on a notebook.

Re: regexp_count seems to not work as it should

Rajasql — Wed, 08 Jan 2025 20:22:40 GMT

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