如何在VARCHAR列的字符与数字之间添加特殊字符？

可以通过正则表达式替换函数实现这个需求，不同数据库的具体语法略有差异，以下是主流数据库的实现方式：

MySQL/MariaDB #

使用REGEXP_REPLACE函数匹配字母与数字、数字与字母的边界，插入下划线：

SELECT REGEXP_REPLACE('ABSC124DBV', '([A-Za-z])([0-9])|([0-9])([A-Za-z])', '$1$3_$2$4') AS formatted_value;

解释：正则表达式([A-Za-z])([0-9])匹配字母后跟数字的场景，([0-9])([A-Za-z])匹配数字后跟字母的场景，替换时在两类边界处插入下划线。

PostgreSQL #

同样使用REGEXP_REPLACE函数，仅反向引用语法略有不同：

SELECT REGEXP_REPLACE('ABSC124DBV', '([A-Za-z])([0-9])|([0-9])([A-Za-z])', '\1\3_\2\4') AS formatted_value;

SQL Server（2017及以上版本） #

SQL Server 2017开始原生支持REGEXP_REPLACE，写法如下：

SELECT REGEXP_REPLACE('ABSC124DBV', '([A-Za-z])([0-9])|([0-9])([A-Za-z])', '$1$3_$2$4') AS formatted_value;

如果是低于2017的版本，可通过自定义函数逐字符判断拼接：

CREATE FUNCTION dbo.AddUnderscores(@input VARCHAR(100))
RETURNS VARCHAR(200)
AS
BEGIN
    DECLARE @output VARCHAR(200) = '';
    DECLARE @i INT = 1;
    DECLARE @len INT = LEN(@input);
    
    WHILE @i <= @len
    BEGIN
        SET @output += SUBSTRING(@input, @i, 1);
        IF @i < @len
        BEGIN
            IF (SUBSTRING(@input, @i, 1) LIKE '[A-Za-z]' AND SUBSTRING(@input, @i+1, 1) LIKE '[0-9]')
                OR (SUBSTRING(@input, @i, 1) LIKE '[0-9]' AND SUBSTRING(@input, @i+1, 1) LIKE '[A-Za-z]')
            BEGIN
                SET @output += '_';
            END
        END
        SET @i += 1;
    END
    RETURN @output;
END;

-- 调用函数
SELECT dbo.AddUnderscores('ABSC124DBV') AS formatted_value;

Oracle #

Oracle的REGEXP_REPLACE语法与PostgreSQL一致：

SELECT REGEXP_REPLACE('ABSC124DBV', '([A-Za-z])([0-9])|([0-9])([A-Za-z])', '\1\3_\2\4') AS formatted_value FROM DUAL;

以上所有方法执行后，输入ABSC124DBV都会得到目标结果ABSC_124_DBV。

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