SQL字符串替换需求：指定区间下划线转@@及SUBSTRING报错解决

需求说明 #

需要处理SQL中的字符串@TEXT，完成两个替换操作：

• 识别LT_与MT_之间的子串，将其中的_替换为@@，并替换原字符串对应部分；
• 识别MT_与_UAN之间的子串，将其中的_替换为@@，并替换原字符串对应部分；
  注意：@TEXT可能仅包含LT_、仅包含MT_，或两者都包含。

输入字符串 #

Note Book_AK47_SINGLE AP/PS_20/40/09/209_6LT_1x2KJU-4ER_400_40S+1x2JHT-4KB_400_40S_3MT_1x4GIE-20KC_400_40P_OLM+2x4MNB-20KC_400_40P_SDF_UAN_-_Customyats_JSHEYH-SH HGSRWT_DVD_CHOT DR6-4P-4W+CB6-2F-2F

期望输出字符串 #

Note Book_AK47_SINGLE AP/PS_20/40/09/209_6LT_1x2KJU-4ER@@400@@40S+1x2JHT-4KB@@400@@40S@@3MT_1x4GIE-20KC@@400@@40P@@OLM+2x4MNB-20KC@@400@@40P@@SDF_UAN_-_Customyats_JSHEYH-SH HGSRWT_DVD_CHOT DR6-4P-4W+CB6-2F-2F

尝试的代码及报错 #

尝试的SQL代码：

Declare @TEXT nvarchar(max) = 'Note Book_AK47_SINGLE AP/PS_20/40/09/209_6LT_1x2KJU-4ER_400_40S+1x2JHT-4KB_400_40S_3MT_1x4GIE-20KC_400_40P_OLM+2x4MNB-20KC_400_40P_SDF_UAN_-_Customyats_JSHEYH-SH HGSRWT_DVD_CHOT DR6-4P-4W+CB6-2F-2F'

Declare @finalResult nvarchar(max) = @TEXT;
Declare @first nvarchar(max) 
SET @first = (SUBSTRING(@TEXT, CHARINDEX('LT_',@TEXT)+3, CHARINDEX('MT_',@TEXT) - (CHARINDEX('LT_',@TEXT)+3))   )

Select @first

报错信息：

Invalid length parameter passed to the LEFT or SUBSTRING function.

报错原因 #

使用CHARINDEX时，如果目标子串不存在会返回0，导致SUBSTRING的长度参数为负数或0，触发错误。另外原代码未考虑@TEXT仅含LT_或仅含MT_的情况，逻辑不完整。

正确SQL实现方案 #

以下代码兼容所有三种场景（仅含LT_、仅含MT_、两者都有），同时避免参数无效问题：

DECLARE @TEXT NVARCHAR(MAX) = 'Note Book_AK47_SINGLE AP/PS_20/40/09/209_6LT_1x2KJU-4ER_400_40S+1x2JHT-4KB_400_40S_3MT_1x4GIE-20KC_400_40P_OLM+2x4MNB-20KC_400_40P_SDF_UAN_-_Customyats_JSHEYH-SH HGSRWT_DVD_CHOT DR6-4P-4W+CB6-2F-2F'
DECLARE @finalResult NVARCHAR(MAX) = @TEXT

-- 处理LT_到MT_之间的替换
IF CHARINDEX('LT_', @finalResult) > 0 AND CHARINDEX('MT_', @finalResult) > 0 AND CHARINDEX('MT_', @finalResult) > CHARINDEX('LT_', @finalResult) + 3
BEGIN
    DECLARE @ltStart INT = CHARINDEX('LT_', @finalResult) + 3
    DECLARE @mtStart INT = CHARINDEX('MT_', @finalResult)
    DECLARE @ltToMtSub NVARCHAR(MAX) = SUBSTRING(@finalResult, @ltStart, @mtStart - @ltStart)
    SET @finalResult = STUFF(@finalResult, @ltStart, @mtStart - @ltStart, REPLACE(@ltToMtSub, '_', '@@'))
END

-- 处理MT_到_UAN之间的替换
IF CHARINDEX('MT_', @finalResult) > 0 AND CHARINDEX('_UAN', @finalResult) > 0 AND CHARINDEX('_UAN', @finalResult) > CHARINDEX('MT_', @finalResult) + 3
BEGIN
    DECLARE @mtStart2 INT = CHARINDEX('MT_', @finalResult) + 3
    DECLARE @uanStart INT = CHARINDEX('_UAN', @finalResult)
    DECLARE @mtToUanSub NVARCHAR(MAX) = SUBSTRING(@finalResult, @mtStart2, @uanStart - @mtStart2)
    SET @finalResult = STUFF(@finalResult, @mtStart2, @uanStart - @mtStart2, REPLACE(@mtToUanSub, '_', '@@'))
END

SELECT @finalResult AS ProcessedText

代码说明 #

1. 条件判断：每次处理前先检查目标子串是否存在，且结束位置在起始位置之后，避免无效的SUBSTRING调用；
2. STUFF函数：精准替换原字符串中的目标子串，替代直接拼接的方式，更简洁可靠；
3. 兼容多场景：分别判断两种替换场景的触发条件，确保在仅含LT_、仅含MT_或两者都有时都能正确执行。

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