Search code examples
replacesplitsnowflake-cloud-data-platformbackslash

how to split string in Snowflake by single backslash

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?

Solution

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