Snowflake with regular expression

Question

Trying to run the below SQL in Snowflake:

SELECT fm_id ,
    CASE
      WHEN regexp_instr(ASSD,'...',1) > 0
      THEN regexp_SUBSTR(ASSD,1,regexp_instr(ASSD,'...',1)-1)
      ELSE ASSD
    END ASSD
    from 
    (SELECT a.fm_id,
      listagg(a.STUID, '; ') within GROUP (
    ORDER BY a.Fm_id, a.STUID ) ASSD
     from stu_d a
     where fm_id = 1222
     group by a.fm_id
     )

Getting error:

"Invalid parameter value: 0. Reason: Position must be positive"

seems it is failing at -1 or 0 value in above case statement.

What am I doing wrong?

Answer 1

Without seeing your table it's hard to say, but regexp_instr() will return 1 when the pattern is at the beginning of the string. Then, you subtract 1, and 0 is an invalid position argument to regexp_substr(). doc

Perhaps you intended to use SUBSTR not REGEX_SUBSTR()