SQL Server 2017优化同列多IIF、CHARINDEX重复数据提取查询

原写法的核心冗余点在于重复执行前缀匹配判断、重复计算相同分隔符的字符位置，既增加代码维护成本，也会带来不必要的字符串扫描开销。可以通过CROSS APPLY把公共计算逻辑前置，所有判断和位置计算只执行一次，后续字段直接引用计算结果即可，最终返回结果和原逻辑完全一致。

实现逻辑 #

• 前置判断MeetingComment是否符合{{{[开头的编码规则，不符合直接返回UNK
• 按顺序计算两个目标方括号块的起止位置：第一个方括号内容为Source，第二个方括号内容为MeetingType，后续方括号块直接忽略，完全匹配给定的提取规则
• 所有位置计算只执行1次，不符合格式的字段直接跳过位置扫描，无重复函数调用

优化后代码 #

SELECT 
  Meeting.MeetingID, 
  Meeting.MeetingComment, 
  Meeting.Subject, 
  Rooms.RoomName, 
  IIF(v.is_valid = 1, SUBSTRING(Meeting.MeetingComment, 5, p1.first_close - 5), 'UNK') AS Source,
  IIF(v.is_valid = 1, SUBSTRING(Meeting.MeetingComment, p2.second_open + 1, p3.second_close - p2.second_open -1), 'UNK') AS MeetingType,
  Meeting.Recurrence, 
  Meeting.Location
-- 保留原有FROM和JOIN逻辑即可，示例：
-- FROM Meeting
-- LEFT JOIN Rooms ON Meeting.RoomID = Rooms.RoomID
CROSS APPLY (
  -- 第一步：判断是否符合编码前缀格式
  SELECT is_valid = CASE WHEN LEFT(Meeting.MeetingComment, 4) = '{{{[' THEN 1 ELSE 0 END
) v
CROSS APPLY (
  -- 第二步：定位Source字段的结束方括号位置
  SELECT first_close = CASE WHEN v.is_valid = 1 THEN CHARINDEX(']', Meeting.MeetingComment, 5) ELSE 0 END
) p1
CROSS APPLY (
  -- 第三步：定位MeetingType字段的开始方括号位置
  SELECT second_open = CASE WHEN v.is_valid = 1 THEN CHARINDEX('[', Meeting.MeetingComment, p1.first_close + 1) ELSE 0 END
) p2
CROSS APPLY (
  -- 第四步：定位MeetingType字段的结束方括号位置
  SELECT second_close = CASE WHEN v.is_valid = 1 THEN CHARINDEX(']', Meeting.MeetingComment, p2.second_open + 1) ELSE 0 END
) p3

方案优势 #

• 代码冗余度大幅降低：前缀判断、位置查找逻辑只写一次，后续如果调整提取规则，只需要修改对应APPLY内的计算逻辑，不需要同步修改多个字段的判断分支
• 性能更优：避免了原写法中对同一字符串多次重复执行CHARINDEX扫描，不符合格式的记录不会执行后续位置计算，数据量越大优化效果越明显
• 规则适配性强：不管第二个方括号内的内容是否包含冒号、空格，也不管后续追加多少个方括号块，都能准确提取到目标值，和样例规则完全匹配
• 容错性一致：MeetingComment为空、NULL、不符合前缀格式时，统一返回UNK，和原有逻辑返回结果无差异

注：SQL Server 2017版本的STRING_SPLIT不支持返回拆分序号，无法稳定保证拆分后的顺序，因此不建议用字符串拆分方式实现，容易出现顺序错乱的问题。

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