splitting text into array or splitting an array into more arrays

Question

I am quite new to working with arrays in snowflake (and dbt). I am splitting out vendor names that I have when they are filled in as join names in our front end. Now, I get them split (see the example below), but I would like to know if it is possible to split an already split array or another function I can use to split the text. In the example below, the first 3 records are split correctly, but the following three, is not being split correctly, and this is where I would appreciate input. The simple case statement I am using at the moment is:

,case
            when upper(vendor_name) like '% AND %' then split(upper(vendor_name), ' AND ')
            when vendor_name like '%&%' then split(upper(vendor_name), ' & ')
        else array_construct(upper(vendor_name))
        end as arr_vendor_name

Answer 1

You can do this in two steps:

• Replace " & ", " AND ", ", " for ",".
• Then split on ",".

split(
  replace(replace(replace(vendor_name, ' AND ', ','), ' & ', ','), ', ', ',')
  , ','
)

Bonus: You won't need the case anymore, and the array_construct goes away too.