Q:

How to find a second/third occurrence in a string in Snowflake/SQL

I'm trying to recreate the FINDNTH function from Tableau into Snowflake/SQL. To use for this formula: lower(ifnull(left([SKU],(FINDNTH([SKU],"_",2)-1)),[SKU]))
https://help.tableau.com/current/pro/desktop/en-us/functions_functions_string.htm
FINDNTH(string, substring, occurrence)
The goal is to find the second occurrence of a character, then return everything left of it. 
Field: CGM415_Black_3XL
Desired Result: CGM415_Black
I've tried using Position and Charaindex 
https://docs.snowflake.net/manuals/sql-reference/functions/position.html
https://docs.snowflake.net/manuals/sql-reference/functions/charindex.html

    Select

    position('_',sku) a,
    position('_',sku,a) b,
    position('_',sku,position('_',sku)) c,
    CHARINDEX('_',sku) d,
    CHARINDEX('_',sku,d) e,
    CHARINDEX('_',sku,CHARINDEX('_',sku)) f,

    sku from DATABASE;

All of these columns (a-f) are showing 7 instead of 13 (if CGM415_Black_3XL was the value).

A:

The following SQL will give you the result
substring(sku,1,regexp_instr(sku,'_',1,2)-1)

regexp_instr(sku,'-',1,2) will give you the position of 2nd _

