Snowflake INSTR

Question

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;

Answer 1

Try this:

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

Answer 2

Alexander's answer is the simplest form, but the REGEXP can be simpler as the ? 0 or 1 is not binding to anything, and the grouping clause (the last 1) can be dropped as it is the default:

select 
    $1 as txt
    ,REGEXP_SUBSTR(txt, '~(.*?)~', 1, 1, 'e', 1) as a1    
    ,REGEXP_SUBSTr(txt, '~(.*)~', 1, 1, 'e', 1) as a2
    ,REGEXP_SUBSTr(txt, '~(.*)~', 1, 1, 'e') as a3
from values
    ('123~~456'),
    ('123~~~456'),
    ('jjjjj~hhhhh-iiiiii~jklmn~abc'),
    ('ftgftr~hhhhhiiiiii~jklmn~trf'),
    ('fgfgf~hhhhhiiiiii~fgfgf~tddrf'),
    ('jjjjj~hhhhh-iiiiii~jklmererereren~abc');

gives:

TXT	A1	A2	A3
123~~456
123~~~456	~	~	~
jjjjj~hhhhh-iiiiii~jklmn~abc	hhhhh-iiiiii~jklmn	hhhhh-iiiiii~jklmn	hhhhh-iiiiii~jklmn
ftgftr~hhhhhiiiiii~jklmn~trf	hhhhhiiiiii~jklmn	hhhhhiiiiii~jklmn	hhhhhiiiiii~jklmn
fgfgf~hhhhhiiiiii~fgfgf~tddrf	hhhhhiiiiii~fgfgf	hhhhhiiiiii~fgfgf	hhhhhiiiiii~fgfgf
jjjjj~hhhhh-iiiiii~jklmererereren~abc	hhhhh-iiiiii~jklmererereren	hhhhh-iiiiii~jklmererereren	hhhhh-iiiiii~jklmererereren