如何在UDF中声明变量以简化多列转换代码？

你之前的写法错误在于使用了表达式型UDF（AS (...)结构），这种UDF只能包含单个SQL表达式，不支持DECLARE、SET这类流程控制语句。要在UDF中定义变量复用逻辑，需要使用块结构UDF，用BEGIN...END包裹逻辑，同时显式声明返回类型。

方案1：使用块结构UDF（支持变量定义） #

这是最适合你场景的方案，尤其适合包含多次嵌套清理、变量复用的情况：

CREATE OR REPLACE FUNCTION mytable.myTEST(VAR1 STRING, VAR2 STRING)
RETURNS STRING
LANGUAGE SQL
AS $$
DECLARE
  -- 声明变量并直接初始化赋值
  X STRING := UPPER(TRIM(VAR1));
  Y STRING := UPPER(TRIM(VAR2));
BEGIN
  -- 复用变量生成结果
  RETURN CONCAT(X, " ", Y);
END;
$$;

如果需要分步赋值，也可以分开声明和赋值：

CREATE OR REPLACE FUNCTION mytable.myTEST(VAR1 STRING, VAR2 STRING)
RETURNS STRING
LANGUAGE SQL
AS $$
DECLARE
  X STRING;
  Y STRING;
BEGIN
  SET X = UPPER(TRIM(VAR1));
  SET Y = UPPER(TRIM(VAR2));
  RETURN CONCAT(X, " ", Y);
END;
$$;

方案2：使用WITH子句简化表达式UDF #

如果不想用块结构，也可以在表达式型UDF中用WITH子句定义临时计算结果，同样能提升可读性：

CREATE OR REPLACE FUNCTION mytable.myTEST(VAR1 STRING, VAR2 STRING) AS (
  WITH processed_values AS (
    SELECT
      UPPER(TRIM(VAR1)) AS X,
      UPPER(TRIM(VAR2)) AS Y
  )
  SELECT CONCAT(X, " ", Y) FROM processed_values
);

调用验证 #

两种方案的UDF都可以直接用你原来的查询语句调用，无需修改：

SELECT 
  ADDR_LINE_1_TXT,
  ADDR_LINE_2_TXT,
  mytable.myTEST(ADDR_LINE_1_TXT, ADDR_LINE_2_TXT) AS ADDRESS
FROM `dataset.table`
WHERE ADDR_LINE_2_TXT IS NOT NULL
  AND ADDR_ID = '2022041517';

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