如何在SELECT查询中高效将字符串字母替换为对应ASCII数值？

针对将字符串中的字母替换为对应ASCII值的需求，以下是几种无需存储过程的高效实现方式，基于主流数据库给出具体示例：

核心思路 #

把目标字符串拆分为单个字符，逐个判断处理：

• 若字符是字母，用ASCII()函数获取其ASCII数值并转为字符串
• 若非字母，直接保留原字符
  最后将所有处理后的字符拼接回完整字符串。

SQL Server 实现 #

递归CTE方式（兼容多数版本）

递归拆分字符串后逐字符处理，再拼接结果：

DECLARE @input NVARCHAR(100) = '1A234';

WITH CharSplit AS (
    SELECT 
        1 AS Position,
        SUBSTRING(@input, 1, 1) AS Char,
        @input AS OriginalString
    UNION ALL
    SELECT 
        Position + 1,
        SUBSTRING(OriginalString, Position + 1, 1),
        OriginalString
    FROM CharSplit
    WHERE Position < LEN(OriginalString)
)
SELECT 
    STRING_AGG(
        CASE WHEN Char LIKE '[A-Za-z]' THEN CAST(ASCII(Char) AS NVARCHAR(2)) ELSE Char END,
        ''
    ) AS ConvertedString
FROM CharSplit
OPTION (MAXRECURSION 0); -- 处理长度超过100的字符串需开启此选项

STRING_SPLIT方式（SQL Server 2016+）

利用字符串拆分函数结合行号保证顺序：

DECLARE @input NVARCHAR(100) = 'AB234';

SELECT 
    STRING_AGG(
        CASE WHEN value LIKE '[A-Za-z]' THEN CAST(ASCII(value) AS NVARCHAR(2)) ELSE value END,
        ''
    ) WITHIN GROUP (ORDER BY rn) AS ConvertedString
FROM (
    SELECT 
        value,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM STRING_SPLIT(@input, '')
    WHERE value <> ''
) t;

Oracle 实现 #

通过正则拆分字符串，使用LISTAGG拼接结果：

WITH CharSplit AS (
    SELECT 
        REGEXP_SUBSTR('1A234', '.', 1, LEVEL) AS Char,
        LEVEL AS Position
    FROM DUAL
    CONNECT BY LEVEL <= LENGTH('1A234')
)
SELECT 
    LISTAGG(
        CASE WHEN REGEXP_LIKE(Char, '[A-Za-z]') THEN TO_CHAR(ASCII(Char)) ELSE Char END,
        ''
    ) WITHIN GROUP (ORDER BY Position) AS ConvertedString
FROM CharSplit;

PostgreSQL 实现 #

拆分字符串后带序号处理，再拼接：

WITH CharSplit AS (
    SELECT 
        unnest(string_to_array('AB234', NULL)) AS Char,
        generate_series(1, length('AB234')) AS Position
)
SELECT 
    string_agg(
        CASE WHEN Char ~ '[A-Za-z]' THEN ascii(Char)::text ELSE Char END,
        ''
    ) AS ConvertedString
FROM CharSplit
GROUP BY TRUE
ORDER BY Position;

方案优势 #

• 无需手动编写大量嵌套REPLACE语句，一次适配所有字母
• 支持任意长度的输入字符串，扩展性强
• 纯SELECT语句实现，无需依赖存储过程或自定义函数

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