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

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?

CodePudding user response：

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)

CodePudding user response：

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