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?

Is abc\def a value from the table or just a literal string you're playing with?. If it was properly escaped, it must have been loaded in as abc\\def, which would work with select split_part('abc\\def','\\',1) — Rajat
– Rajat, Commented Feb 2, 2023 at 14:47
it's a field value we transferred from another source to Snowflake — Oleksiy Kustlyvy
– Oleksiy Kustlyvy, Commented Feb 2, 2023 at 14:53
When you run a select on the snowflake table do you see abc\def or abcdef? — Rajat
– Rajat, Commented Feb 2, 2023 at 15:07
If you are looking for only one '\' and you want to split into 2 words then you can try this. select 'abc/def',SUBSTR( 'abc/def',1, regexp_instr( 'abc/def', '/')-1 ) first_w, SUBSTR( 'abc/def', regexp_instr( 'abc/def', '/')+1 ) secound_w from dual ; — Himanshu Kandpal
– Himanshu Kandpal, Commented Feb 2, 2023 at 15:20

NickW · Accepted Answer · 2023-02-02 15:23:47Z

2

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" ]

answered Feb 2, 2023 at 15:23

NickW

10k2 gold badges10 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

thank you, it really stores the value in a proper splittable format but I've tried that debug on the wrong string thank you!