CONNECT BY in Snowflake query

Question

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 also supports the CONNECT BY syntax with support for LEVEL. https://docs.snowflake.com/en/sql-reference/constructs/connect-by.html — Greg Pavlik, Dec 03 '21 at 15:42

Lukasz Szozda · Accepted Answer · 2021-12-03T16:15:00.237

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).

CONNECT BY in Snowflake query

1 Answers1