Regular expression in Snowflake - starts with string and ends with digits-CodePudding

I am struggling with writing regex expression in Snowflake.

SELECT
 'DEM7BZB01-123' AS SKU,
 RLIKE('DEM7BZB01-123','^DEM.*\d\d$') AS regex

I would like to find all strings that starts with "DEM" and ends with two digits. Unfortunately the expression that I am using returns FALSE.

I was checking this expression in two regex generators and it worked.

CodePudding user response：

In snowflake the backslash character \ is an escape character.
Reference: Escape Characters and Caveats

So you need to use 2 backslashes in a regex to express 1.

SELECT
 'DEM7BZB01-123' AS SKU,
 RLIKE('DEM7BZB01-123', '^DEM.*\\d\\d$') AS regex

Or you could write the regex pattern in such a way that the backslash isn't used.

For example, the pattern ^DEM.*[0-9]{2}$ matches the same as the pattern ^DEM.*\d\d$.

CodePudding user response：

You need to escape your backslashes in your SQL before it can be parsed as a regex string. (sometimes it gets a bit silly with the number of backslashes needed)

Your example should look like this

RLIKE('DEM7BZB01-123','^DEM.*\\d\\d$') AS regex