CONNECT BY in Snowflake query

user_5237868 0
Viewed 220

I have a SQL query that runs fine in Oracle. It uses LEVEL and CONNECT BY in query. How I can convert this query so that I can run it in Snowflake db.

Select level, regexp_substr('AAAA,BBBB,CCCC','[^,]+',1,level) as COL1 from dual
connect by regexp_substr('AAAA,BBBB,CCCC','[^,]+',1,level) is not null;
snowflake-cloud-data-platform
1 Answers

This particular query seems to be an attempt to implement string split(Related: Splitting string into multiple rows in Oracle):

Select level, regexp_substr('AAAA,BBBB,CCCC','[^,]+',1,level) as COL1 
from dual 
connect by regexp_substr('AAAA,BBBB,CCCC','[^,]+',1,level) is not null;

/*
    LEVEL   COL1
1   AAAA
2   BBBB
3   CCCC
*/

db<>fiddle demo

Snowflake supports natively recursive queries(both in a form of CONNECT BY and recursive CTE).

But it is much easier to achieve it using built-in SPLIT_TO_TABLE:

SELECT t.index, t.value
FROM TABLE(SPLIT_TO_TABLE('AAAA,BBBB,CCCC', ',')) AS t;

Sidenote: Literal translations are not always the best approach.

A general rule for migrating any kind of code between different systems: behaviour first(WHY/WHAT) and then implementation(HOW).

user_50708790
Related