在AWS athena中,当我使用转换函数时,我不断收到以下错误
select convert(bigint,(split_part(address,'.',1)))
+ convert(bigint,(split_part(address,'.',2))) as sum
from table
错误代码:SYNTAX_Error:行2:9:无法解析列"bigint">
例如,address的值是11.22,所以我想返回11和22作为bigint,返回33作为sum。有人能解决这个问题吗?我被卡住了
使用CAST
SELECT
CAST(split_part('11.22','.',1) AS INTEGER)
+ CAST(split_part('11.22','.',2) AS INTEGER) AS sum
更清洁和可扩展的解决方案可以使用regexp_extract_all和reduce
SELECT reduce(regexp_extract_all('11.22', 'd+'),
0,
(s, x) -> CAST(s AS INTEGER) + CAST(x AS INTEGER),
s -> s)