如何在Snowflake中将字符串拆分为字符? [英] How can I split a string into character in Snowflake?
问题描述
我需要分割一个字符串,例如"abc".分为单个记录,例如"a","b","c".
这在Snowflake中应该很容易: SPLIT(str,delimiter)
但是,如果分隔符为null或空字符串,则会得到完整的 str
,而不是我期望的字符.
更新:SQL UDF
创建或替换函数split_string_to_char(字符串)返回数组作为$$split(regexp_replace(a,'.',',\\ 0',2),',')$$;选择split_string_to_char('hello');
我在处理
如果要从中创建表:
<代码>选择*来自表(split_to_table(regexp_replace('abc','.',',\\ 0',2),','))y
在
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.
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
这篇关于如何在Snowflake中将字符串拆分为字符?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!