how to split string in Snowflake by single backslash

Question

have a problem I can't solve directly using Snowflake docs:

I have a strings like 'abc\def'

need to split it to 'abc', 'def'

tried: split_to_table('abc\def', '\\') - error

strtok_to_array('abc\def', '\\') ==> [ "abcdef" ]

also, I've tried to replace it to better delimiter prior the split

replace('abc\cde','\\','_another_symbol_'); ==> abccde REGEXP_REPLACE('abc\cde','$$\$$','_another_symbol_') ==> abccde_another_symbol

but it doesn't work

any idea how to solve that?

Answer 1

If you just use SPLIT it will split the values into an array which you can then process however you want e.g.

with dataset as (
     select $1 as col1 from
     (values
     ('abc\\def'),
     ('ghi\\jkl'))
 )
 select col1, split(col1,'\\')
 from dataset

COL1	SPLIT(COL1,'\')
abc\def	[ "abc", "def" ]
ghi\jkl	[ "ghi", "jkl" ]