How to convert this Vertica SQL containing regexp_substr to Snowfake one?

Paul Z Wu 0
Viewed 30

The monitors wanted to close this question, but I think it may be helpful to keep it since this question highlights the differences between different SQL systems (Veritica and Snowflake in this case), the exactly same expression produces different results on different platforms. Also the reg function is pretty common in db systems, but in Spark, it seems the closest function to this one is regep_extract.

On Vertica:

select regexp_substr('5,161,361,6,4,7,65,8,220,9,138' ,'^\d+(,\d+,\d+)*,161,(\d+)',1,1,'',2)

will return 361. But on Snowflake, this returns null. Could anyone help?

vertica snowflake-cloud-data-platform regex
2 Answers

One of my colleagues helped me. Just need a second backslash before '\d' as follows:

select regexp_substr('5,161,361,6,4,7,65,8,220,9,138' ,'^\\d+(,\\d+,\\d+)*,161,(\\d+)',1,1,'',2)
Paul Z Wu0

If the goal is to get third value then SPLIT_PART is much easier:

SELECT SPLIT_PART('5,161,361,6,4,7,65,8,220,9,138', ',', 3)
-- 361
Lukasz Szozda0
Related