How to use regular expression in snowflake?

Question

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

Mike Walton · Accepted Answer · 2020-04-23 13:28:31Z

1

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

edited Apr 23, 2020 at 13:28

Mike Walton

7,4592 gold badges14 silver badges26 bronze badges

answered Apr 23, 2020 at 13:08

MMV

9906 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Garrison Over a year ago

there's also a shorthand for {1,} which is +, So R\\d+ .* also works

Collectives™ on Stack Overflow

How to use regular expression in snowflake?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related