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

Question

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).

demircioglu demircioglu · Accepted Answer · 2019-11-07T19:11:19

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 _

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

1 Answers