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;
Try this:
SELECT REGEXP_SUBSTR('jjjjj~hhhhh-iiiiii~jklmererereren~abc', '~(.*?)~', 1, 1, 'e', 1) AS extracted_string;