MS SQL单条UPDATE语句为指定字符串前后加空格且避免重复处理

需求说明 #

需要将表中类似 £140,000effective from01-NOV-18 的字符串，在effective from前后各插入一个空格，变成 £140,000 effective from 01-NOV-18；同时只处理未修改过的行——也就是仅当字符串里只有1个空格时才执行更新（原未处理行里仅effective from中间有1个空格，处理后会新增两个空格，总空格数变为3）。

解决方案 #

用REPLACE完成字符串替换，结合空格数量筛选条件锁定目标行：

UPDATE YourTable
SET YourColumn = REPLACE(YourColumn, 'effective from', ' effective from ')
WHERE 
    -- 统计空格总数，仅匹配只有1个空格的未处理行
    LEN(YourColumn) - LEN(REPLACE(YourColumn, ' ', '')) = 1
    -- 额外校验确保行包含目标字符串，避免误更新
    AND CHARINDEX('effective from', YourColumn) > 0

细节说明 #

1. LEN(YourColumn) - LEN(REPLACE(YourColumn, ' ', ''))：通过计算原字符串长度与移除所有空格后的长度差，得到空格总数，精准锁定未处理的行。
2. CHARINDEX('effective from', YourColumn) > 0：避免那些空格数为1但不含目标字符串的行被误更新，提升操作安全性。

示例验证 #

原数据：

£140,000effective from01-NOV-18
£40,000effective from01-NOV-18
£12,000effective from01-NOV-19

执行语句后的数据：

£140,000 effective from 01-NOV-18
£40,000 effective from 01-NOV-18
£12,000 effective from 01-NOV-19

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