In snowflake I try to get the first occurrence of the values between -> and -> in a column

I use regexp_substr with the following code, which return ->ABCD-> and not just ABCD

select regexp_substr('98547279->ABCD->2554785->DEFT', '->[[:alnum:]\-] ->') AS "SUBSTRING";

How can I prevent to get the -> -> in the result?

CodePudding user response：

Try it with a group ()

select regexp_substr('98547279->ABCD->2554785->DEFT',
                     '->([[:alnum:]\-] )(->|$)', 1, 1, 'e', 1) AS "SUBSTRING"

CodePudding user response：

If regexp is not a must, then SPLIT_PART does the job:

SELECT SPLIT_PART('98547279->ABCD->2554785->DEFT', '->', 1) AS result
-- ABCD