regexp_count seems to not work as it should

Rajasql
New Contributor II

‎01-08-2025 11:41 AM

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;
0 Kudos

Walter_C
Databricks Employee
Databricks Employee

‎01-08-2025 12:09 PM

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

0 Kudos

Rajasql
New Contributor II
In response to Walter_C

‎01-08-2025 12:20 PM

It is being executed on a notebook.

0 Kudos

PabloCSD
Valued Contributor II

‎01-08-2025 12:17 PM

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;

View solution in original post

Rajasql
New Contributor II
In response to PabloCSD

‎01-08-2025 12:22 PM

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
0 Kudos