regexsnowflake-cloud-data-platforminstr

Snowflake INSTR


I am trying to extract the values in a string before the last ~ and first ~ using Snowflake

For example:

‘jjjjj~hhhhh-iiiiii~jklmn~abc’

‘ftgftr~hhhhhiiiiii~jklmn~trf’

‘fgfgf~hhhhhiiiiii~fgfgf~tddrf’

'jjjjj~hhhhh-iiiiii~jklmererereren~abc'

Expected output:

hhhhh-iiiiii~jklmn

hhhhhiiiiii~jklmn

hhhhhiiiiii~fgfgf

hhhhh-iiiiii~jklmererereren

My query is trimming some of the values. Is there another option to achieve the above results?


select SUBSTR('jjjjj~hhhhh-iiiiii~jklmererereren~abc',regexp_instr('jjjjj~hhhhh-iiiiii~jklmererereren~abc', '[~]',1,1)+1,regexp_instr('jjjjj~hhhhh-iiiiii~jklmererereren~abc', '[~]',1,2)) from dual;

Solution

  • Try this:

    SELECT REGEXP_SUBSTR('jjjjj~hhhhh-iiiiii~jklmererereren~abc', '~(.*?)~', 1, 1, 'e', 1) AS extracted_string;