How can I split a string into character in Snowflake?

Question

I need to split a string like "abc" into individual records, like "a", "b", "c".

This should be easy in Snowflake: SPLIT(str, delimiter)

But if the delimiter is null, or an empty string I get the full str, and not characters as I expected.

Answer 1

You can use regexp_extract_all() function in Snowflake to extract every char into an array.

See the following code as an example:

regexp_extract_all('abc','.{1}')

Answer 2

Update: SQL UDF

create or replace function split_string_to_char(a string)
returns array
as $$
split(regexp_replace(a, '.', ',\\0', 2), ',')
$$
;
select split_string_to_char('hello');

---

I found this problem while working on Advent of Code 2020.

Instead of just splitting a string a working solution is to add commas between all the characters, and then split that on the commas:

select split(regexp_replace('abc', '.', ',\\0', 2), ',')

If you want to create a table out of it:

select *
from table(split_to_table(regexp_replace('abc', '.', ',\\0', 2), ',')) y

As seen on https://github.com/fhoffa/AdventOfCodeSQL/blob/main/2020/6.sql

Answer 3

In addition to Felipe's approach, you could also use a JavaScript UDF:

create function TO_CHAR_ARRAY(STR string)
returns array
language javascript
as
$$
    return STR.split('');
$$;

select to_char_array('hello world');