DB2中实现SQL Server Latin1_General_CI_AI等效的大小写不敏感替换

错误方法说明 #

你尝试使用的COLLATION_KEY_BIT函数返回的是用于排序的二进制键，并非原字符串的转换形式，因此无法直接用于字符串替换操作；同时CLDR2701_EX_S1排序规则可能不在你的DB2版本支持列表中，这是报错的核心原因。以下是针对不同DB2版本的正确实现方案：

DB2 LUW 10.5 实现方式 #

方法1：正则表达式替换（推荐） #

利用REGEXP_REPLACE的不区分大小写标志，一次性替换所有大小写形式的foo为bar，同时保留其他字符的原始大小写：

UPDATE MyTable
SET MyColumn = REGEXP_REPLACE(MyColumn, 'foo', 'bar', 1, 0, 'i')

• 参数解释：
  • 第5个参数0：替换所有匹配的目标字符串
  • 第6个参数'i'：启用不区分大小写匹配模式

方法2：循环定位替换（兼容复杂场景） #

若正则表达式支持受限，可通过递归CTE结合LOCATE_IN_STRING实现循环替换：

WITH RECURSIVE cte AS (
    SELECT 
        MyTableID, 
        MyColumn,
        LOCATE_IN_STRING(MyColumn, 'foo', 1, 0) AS match_pos
    FROM MyTable
    WHERE LOCATE_IN_STRING(MyColumn, 'foo', 1, 0) > 0
    UNION ALL
    SELECT 
        MyTableID,
        SUBSTR(MyColumn, 1, match_pos-1) || 'bar' || SUBSTR(MyColumn, match_pos+3),
        LOCATE_IN_STRING(SUBSTR(MyColumn, 1, match_pos-1) || 'bar' || SUBSTR(MyColumn, match_pos+3), 'foo', match_pos+3, 0)
    FROM cte
    WHERE match_pos > 0
)
UPDATE MyTable t
SET MyColumn = c.MyColumn
FROM cte c
WHERE t.MyTableID = c.MyTableID;

• LOCATE_IN_STRING的第4个参数0：启用不区分大小写匹配

IBM i（iSeries）7.1/7.2/7.3 实现方式 #

方法1：COLLATE子句结合LOCATE（全版本兼容） #

通过指定不区分大小写的排序规则定位目标内容，再完成替换：

UPDATE MyTable
SET MyColumn = CASE
    WHEN LOCATE('foo', MyColumn COLLATE SQL_UPPER) > 0 THEN
        SUBSTR(MyColumn, 1, LOCATE('foo', MyColumn COLLATE SQL_UPPER)-1) || 'bar' || SUBSTR(MyColumn, LOCATE('foo', MyColumn COLLATE SQL_UPPER)+3)
    ELSE MyColumn
END
WHERE LOCATE('foo', MyColumn COLLATE SQL_UPPER) > 0;

• SQL_UPPER排序规则：忽略字符大小写进行匹配，确保捕获FOO/Foo/foo等所有形式

方法2：正则表达式替换（7.2及以上版本支持） #

IBM i 7.2及以上版本支持REGEXP_REPLACE，用法与DB2 LUW一致：

UPDATE MyTable
SET MyColumn = REGEXP_REPLACE(MyColumn, 'foo', 'bar', 1, 0, 'i')

验证建议 #

执行更新前，先通过SELECT语句验证替换结果，避免误操作：

SELECT 
    MyColumn, 
    REGEXP_REPLACE(MyColumn, 'foo', 'bar', 1, 0, 'i') AS replaced_column
FROM MyTable
WHERE MyColumn LIKE '%foo%' COLLATE SQL_UPPER;

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