请求协助将MSSQL移除Varchar列数字的函数转换为PostgreSQL可用函数

首先得说，你原来的MSSQL思路在PostgreSQL里需要调整——PostgreSQL的SQL语言不支持循环这类流程控制，而且也没有PatIndex和Stuff这些MSSQL特有的函数。不过PostgreSQL的正则表达式功能很强，我们可以用更简洁高效的方式实现你的需求。

方案一：使用正则表达式（推荐，高效简洁） #

PostgreSQL的regexp_replace函数可以直接全局替换所有数字字符，一步到位，完全不需要循环。函数代码如下：

CREATE OR REPLACE FUNCTION public.RemoveNumericCharacters(Temp VARCHAR(1000))
RETURNS VARCHAR(1000) AS $$
BEGIN
    -- 全局替换所有数字为空字符串
    RETURN regexp_replace(Temp, '[0-9]', '', 'g');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

参数解释：

• '[0-9]'：匹配所有0-9的数字字符
• ''：将匹配到的内容替换为空
• 'g'：全局替换标志，确保替换字符串中所有的数字，而不是只替换第一个

测试你的示例场景：

SELECT public.RemoveNumericCharacters('testuser345@hotmail.com');

执行后会直接返回testuser@hotmail.com，完美符合你的需求。

方案二：模拟MSSQL的循环逻辑（不推荐，效率较低） #

如果你一定要用类似MSSQL的循环思路实现，那得切换到plpgsql语言（PostgreSQL的SQL语言不支持循环），代码如下：

CREATE OR REPLACE FUNCTION public.RemoveNumericCharacters(Temp VARCHAR(1000))
RETURNS VARCHAR(1000) AS $$
DECLARE
    num_position INT;
BEGIN
    LOOP
        -- 找到第一个数字的位置，替代MSSQL的PatIndex
        num_position := position(ANY(ARRAY['0','1','2','3','4','5','6','7','8','9']) IN Temp);
        EXIT WHEN num_position = 0; -- 没有数字就退出循环
        
        -- 删除找到的数字，替代MSSQL的Stuff
        Temp := overlay(Temp placing '' from num_position for 1);
    END LOOP;
    RETURN Temp;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

不过这个方案的效率远不如正则表达式，尤其是处理长字符串或大量数据时，所以更推荐第一种方案。

额外优化建议 #

• 加上IMMUTABLE标志：因为这个函数的输出完全由输入决定，没有副作用，PostgreSQL会对它进行性能优化（比如缓存结果）。
• 灵活调整类型：如果你的字段长度超过1000，可以把VARCHAR(1000)改成TEXT类型，适配更长的字符串。

内容的提问来源于stack exchange，提问作者sc-leeds