在Snowflake中创建ISNUMBER自定义函数的代码修正请求

Snowflake没有内置的ISNUMBER函数，我们可以参考Oracle的实现逻辑来创建自定义函数。

Oracle中的ISNUMBER示例代码 #

CREATE OR REPLACE FUNCTION is_number (p_string IN VARCHAR2)
  RETURN INT
IS
  v_num NUMBER;
BEGIN
  v_num := TO_NUMBER(p_string);
  RETURN 1;
EXCEPTION
WHEN VALUE_ERROR THEN
  RETURN 0;
END is_number;

原Snowflake代码的问题 #

你编写的Snowflake代码存在以下问题：

• Snowflake的SQL语言UDF不支持DECLARE变量声明和EXCEPTION异常捕获逻辑，这类语法属于PL/SQL风格，SQL UDF仅支持纯SQL表达式。
• 参数类型VARCHAR2是Oracle专属类型，Snowflake中应使用标准的VARCHAR类型。
• statement_error并非Snowflake SQL UDF可用的异常类型，且SQL UDF本身不支持异常捕获机制。

修正后的实现方案 #

方案1：使用TRY_TO_NUMBER的SQL UDF（推荐） #

Snowflake内置了TRY_TO_NUMBER函数，转换失败时会返回NULL，我们可以利用这个特性快速实现ISNUMBER：

CREATE OR REPLACE FUNCTION ISNUMBER(p_string VARCHAR)
RETURNS INT
LANGUAGE SQL
AS
$$
    CASE WHEN TRY_TO_NUMBER(p_string) IS NOT NULL THEN 1 ELSE 0 END
$$;

方案2：使用JavaScript UDF（模拟异常捕获逻辑） #

如果想要更贴近Oracle的异常捕获写法，可以用JavaScript UDF实现：

CREATE OR REPLACE FUNCTION ISNUMBER(p_string VARCHAR)
RETURNS INT
LANGUAGE JAVASCRIPT
AS
$$
    function main(p_string) {
        try {
            Number(p_string);
            return 1;
        } catch (e) {
            return 0;
        }
    }
$$;

测试验证 #

可以用以下语句验证函数效果：

SELECT ISNUMBER('123') AS num_test, ISNUMBER('abc') AS non_num_test;

执行后会返回num_test=1，non_num_test=0，符合预期。

内容的提问来源于stack exchange，提问作者Noob