创建临时函数实现sub_org列区域团队名称缩写替换

针对你的需求，下面提供几种主流数据库的临时函数实现方案，方便你复用这个替换逻辑：

PostgreSQL 版本 #

创建会话级临时函数，匹配目标字符串返回对应缩写：

CREATE OR REPLACE TEMP FUNCTION shorten_sub_org(org_name TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN CASE 
        WHEN org_name = 'Regional Team Americas' THEN 'AMER'
        WHEN org_name = 'Regional Team EMEA' THEN 'EMEA'
        WHEN org_name = 'Regional Team APAC' THEN 'APAC'
        ELSE org_name -- 未匹配到的内容保留原值
    END;
END;
$$ LANGUAGE plpgsql;

调用函数查询：

SELECT shorten_sub_org(sub_org) AS sub_org_abbr, * FROM your_table;

MySQL 版本 #

MySQL的临时函数仅在当前会话有效，写法如下：

DELIMITER //
CREATE TEMPORARY FUNCTION shorten_sub_org(org_name VARCHAR(255))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE abbr VARCHAR(10);
    SET abbr = CASE
        WHEN org_name = 'Regional Team Americas' THEN 'AMER'
        WHEN org_name = 'Regional Team EMEA' THEN 'EMEA'
        WHEN org_name = 'Regional Team APAC' THEN 'APAC'
        ELSE org_name
    END;
    RETURN abbr;
END //
DELIMITER ;

调用示例：

SELECT shorten_sub_org(sub_org) AS sub_org_abbr, * FROM your_table;

SQL Server 版本 #

创建标量临时函数处理替换逻辑：

CREATE OR ALTER FUNCTION dbo.shorten_sub_org(@org_name NVARCHAR(255))
RETURNS NVARCHAR(10)
AS
BEGIN
    DECLARE @abbr NVARCHAR(10);
    SET @abbr = CASE
        WHEN @org_name = 'Regional Team Americas' THEN 'AMER'
        WHEN @org_name = 'Regional Team EMEA' THEN 'EMEA'
        WHEN @org_name = 'Regional Team APAC' THEN 'APAC'
        ELSE @org_name
    END;
    RETURN @abbr;
END;

使用函数查询：

SELECT dbo.shorten_sub_org(sub_org) AS sub_org_abbr, * FROM your_table;

额外提示 #

如果只是单次查询需要替换，不用创建函数也能实现，直接用CASE语句即可：

SELECT 
    CASE sub_org
        WHEN 'Regional Team Americas' THEN 'AMER'
        WHEN 'Regional Team EMEA' THEN 'EMEA'
        WHEN 'Regional Team APAC' THEN 'APAC'
        ELSE sub_org
    END AS sub_org_abbr,
    *
FROM your_table;

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