Convert Oracle query to snowflake

1
votes

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

1
snowflake-cloud-data-platform
can you post an example input and an expected output. because 'abc >> ' as con_name seems to be working for me, so I am not sure what you have that you think should work but is not working.. – Simeon Pilgrim
Welcome to SO! I think you could improve your chances of getting an answer with a minimal reproducible example – DCTID
Can you please share your sample oracle input. – Sriga
you want first field ? select REGEXP_SUBSTR('test1 >> test2 >> test3' , '[^>>]+' ,1) as val; – PIG

1 Answers

1
votes

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.