3

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;
Sergiu
  • 2,436
  • 1
  • 8
  • 16
Nid
  • 93
  • 5
  • 1
    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

1 Answers1

1

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

Lukasz Szozda
  • 139,860
  • 19
  • 198
  • 228