CODEWITHSUNDEEP
snowflake-cloud-data-platform
redcenteralice
redcenteralice

Reputation: 1

How to use regular expression in snowflake?

Rule: 1. Start with R ; 2. One or more number ; 3. One space ; 4. Follow with other characters ;

Test case: Input : 'R1 ABC' 'R4 DEF' 'Randwick Acca' 'R11 PPP'

Expect Output : 'R1 ABC' 'R4 DEF' 'R11 PPP'

Regular expression : "R\d{1,} " I use regular expression tester, it works. https://regex-golang.appspot.com/assets/html/index.html?_sm_au_=iHVPMjQb0QjFkMTHfLJ4vK7214sJW

Test query:

WITH tbl 
AS (select t.column1 mycol from values('R1 ABC'),('R4 DEF'),('Randwick Acca'),('R11 PPP') t)
SELECT *
FROM tbl 
WHERE mycol regexp 'R\d{1,} ' ;

Return NULL .

Thanks, Bin

Upvotes: 0

Views: 1316

Answers (1)

MMV
MMV

Reputation: 980

1) where's the "any other character"? Because what you have ends with space, period

2) welcome to SQL. \ is a special character and needs to be escaped

So:

WHERE mycol regexp 'R\\d{1,} .*';

I tested it on your query and it seemed to work

Upvotes: 1

Related Questions