创建REGEX自定义函数优化查询速度及报错问题咨询

存在的核心问题 #

• GET函数使用错误：GET需要传入2个参数（对象 + 要获取的键名），你的调用只传入了1个参数，直接触发参数不足报错。
• 自定义函数逻辑完全不符合需求：
  • 入参和正则逻辑搞反：你需要把传入的字段值作为匹配目标，而不是把字段值作为正则表达式本身
  • 返回类型错误：你定义返回OBJECT类型，但实际需要返回提取到的数字字符串，不需要返回对象
  • JS语法错误：JS的RegExp构造函数只接受2个参数（正则表达式、匹配标志），你传入了多余的无效参数
• 性能选择错误：JS UDF的执行效率远低于原生SQL函数，没必要用JS实现这个简单的正则提取逻辑。

最优修正方案 #

推荐使用原生SQL自定义函数，性能和你原来的手写正则语句一致，同时可以复用逻辑简化代码：

1. 创建正确的自定义函数 #

CREATE OR REPLACE FUNCTION dfp.extract_v_code(input_str VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
STRICT
AS '
-- 最后一个参数1表示直接返回第一个捕获组的内容，也就是[v=后面的数字
REGEXP_SUBSTR(input_str, ''[[]v=([0-9]+)'', 1, 1, ''ie'', 1)
';

2. 调用简化后的查询语句 #

不需要GET函数，直接调用UDF即可：

COALESCE(
    dfp.extract_v_code(NAME),
    dfp.extract_v_code(NAME_5),
    dfp.extract_v_code(NAME_4),
    dfp.extract_v_code(NAME_3),
    dfp.extract_v_code(NAME_2),
    dfp.extract_v_code(NAME_1),
    dfp.extract_v_code(NAME_0)
) as display_vertical_code

可选JS UDF实现（不推荐，性能更低） #

如果你一定要用JS实现，正确写法如下：

CREATE OR REPLACE FUNCTION dfp.extract_v_code_js(input_str VARCHAR)
RETURNS VARCHAR
LANGUAGE javascript 
STRICT
AS '
const matchRes = INPUT_STR.match(/\[v=([0-9]+)/i);
return matchRes ? matchRes[1] : null;
';

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