Extract substring from file path in snowflake

Question

I need to extract substring from list of file paths. Paths are like "\rootfolder\subfolder". Need extract substring as "rootfolder" ( between 2nd backslash and 3rd backslash.

select SUBSTR('\\rootfolder\subfolder\', 3, (REGEXP_INSTR('\\rootfolder\subfolder\','\', 1, 3) - 3)) from table;

But this expression didn't give right result in Snowflake. problem is with backslash character.

Answer 1

Using SPLIT_PART:

SELECT SPLIT_PART($$\\rootfolder\subfolder\$$, $$\$$, 3);
--
SELECT SPLIT_PART('\\\\rootfolder\\subfolder\\', '\\', 3);

Output:

Dollar quoting was used to avoid doubling \.