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