CODEWITHSUNDEEP
snowflake-cloud-data-platform
Sacred Games
Sacred Games

Reputation: 65

Convert Oracle query to snowflake

I am facing an issue while converting a query from Oracle to snowflake. Could you please help out.

Sample Oracle query: replace(REGEXP_SUBSTR( col_name,'(.*?)([[:space:]]>>[[:space:]]|$)', 1,1 ) , ' >> ','') as test

Upvotes: 1

Views: 285

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10069

It seems Snowflake behaves different when processing (.*?) part of your regular expression. As a workaround, you may use [^>]* or \w+ instead of (.*?):

SELECT
replace(REGEXP_SUBSTR( 'test1 >> test2 >> test3','([^>]*)([[:space:]]>>[[:space:]]|$)', 1,1 ) , ' >> ','') as test;

SELECT
replace(REGEXP_SUBSTR( 'test1 >> test2 >> test3','\\w+([[:space:]]>>[[:space:]]|$)', 1,1) , ' >> ','') as test;

These should give the same result ("test1") with Oracle's REGEXP_SUBSTR.

Upvotes: 1

Related Questions