如何将SQL字符串解析函数内联至长查询以适配新系统？

我们正在把一条包含复杂字符串解析函数的长SQL SELECT语句移植到新系统，但新平台不支持移植外部函数，需要把[dbo].fn_parseformattedstring函数内联到查询里。

原查询伪代码 #

SELECT 
    812_ACT_RM.R1_ACT,
    812_ACT_RM.R1_FIRST,
    -- ... 其他更多字段
JOIN 
    (SELECT 
         W_N9R.W_N9_IDSF, 
         dbo.fn_parseformattedstring(1, W_N9R.W_N9_IDSF) AS KOX
    )
    -- ... 其他更多关联逻辑

原函数定义 #

create function [dbo].fn_parseformattedstring(@format_id int,@str_in as varchar(max))
returns varchar(200)
as
begin
declare @str_out as varchar(max)
declare @idx integer
declare @idy integer

set @str_out = ''
If @format_id = 1 -- Date(s): {October 4, 5, 6 and December 8, 2010}  Time:
begin
        set @idx = CHARINDEX('Date(s):', @str_in)
        If @idx > 0 
        begin
           If len(@str_in) >  @idx + len('Date(s):')
           begin
              set @idx = @idx+ len('Date(s):')
              set @idy = CHARINDEX('Time',@str_in,@idx)  -- added @idx parameter
              If @idy > 0  
                set @str_out = RTRIM(LTRIM(SUBSTRING(@str_in, @idx, @idy-@idx)))
            end
        end
                
        set @idx = CHARINDEX('Date:', @str_in)
        If @idx > 0 
        begin
           If len(@str_in) >  @idx + len('Date:')
           begin
              set @idx = @idx+ len('Date:')
              set @idy = CHARINDEX('Time',@str_in,@idx)
              If @idy > 0  
                set @str_out = RTRIM(LTRIM(SUBSTRING(@str_in, @idx, @idy-@idx)))
            end
        end 
            
        set @idx = CHARINDEX('Dates:', @str_in)
        If @idx > 0 
        begin
           If len(@str_in) >  @idx + len('Dates:')
           begin
              set @idx = @idx+ len('Dates:')
              set @idy = CHARINDEX('Time',@str_in,@idx)
              If @idy > 0  
                set @str_out = RTRIM(LTRIM(SUBSTRING(@str_in, @idx, @idy-@idx)))
            end
        end 
            
end

If @format_id = 2 -- Time: {8:30 AM - 3:30 PM}  Location:
begin

  set @idx = CHARINDEX('Time(s):',@str_in)
  If @idx > 0
  begin
    If len(@str_in) >  @idx + len('Time(s):')
    begin
       set @idx = @idx+len('Time(s):')
       set @idy = CHARINDEX('Location:',@str_in,@idx)
       If @idy > 0  
        set @str_out = RTRIM(LTRIM(SUBSTRING(@str_in, @idx, @idy-@idx)))
     end
   end
   
  set @idx = CHARINDEX('Time:',@str_in)
  If @idx > 0 
  begin
    If len(@str_in) >  @idx + len('Time:')
    begin
       set @idx = @idx+len('Time:')
       set @idy = CHARINDEX('Location',@str_in,@idx)
       If @idy > 0  
        set @str_out = RTRIM(LTRIM(SUBSTRING(@str_in, @idx, @idy-@idx)))
     end
   end
   
  set @idx = CHARINDEX('Times:',@str_in)
  If @idx > 0 
  begin
    If len(@str_in) >  @idx + len('Times:')
    begin
     set @idx = @idx+len('Times:')
     set @idy = CHARINDEX('Location',@str_in,@idx)
     If @idy > 0  
     set @str_out = RTRIM(LTRIM(SUBSTRING(@str_in, @idx, @idy-@idx)))
     end
   end
   
   
end
   return  cast(@str_out as varchar(200))
end

内联后的查询（针对format_id=1的情况） #

直接替换原查询中调用函数的部分，复刻原函数逻辑：

SELECT 
    812_ACT_RM.R1_ACT,
    812_ACT_RM.R1_FIRST,
    -- ... 其他更多字段
JOIN 
    (SELECT 
         W_N9R.W_N9_IDSF,
         -- 内联format_id=1的解析逻辑
         CAST(
             CASE
                 -- 先匹配'Date(s):'的情况
                 WHEN CHARINDEX('Date(s):', W_N9R.W_N9_IDSF) > 0 
                      AND LEN(W_N9R.W_N9_IDSF) > CHARINDEX('Date(s):', W_N9R.W_N9_IDSF) + LEN('Date(s):')
                      AND CHARINDEX('Time', W_N9R.W_N9_IDSF, CHARINDEX('Date(s):', W_N9R.W_N9_IDSF) + LEN('Date(s):')) > 0
                 THEN RTRIM(LTRIM(SUBSTRING(W_N9R.W_N9_IDSF, 
                                           CHARINDEX('Date(s):', W_N9R.W_N9_IDSF) + LEN('Date(s):'),
                                           CHARINDEX('Time', W_N9R.W_N9_IDSF, CHARINDEX('Date(s):', W_N9R.W_N9_IDSF) + LEN('Date(s):')) 
                                           - (CHARINDEX('Date(s):', W_N9R.W_N9_IDSF) + LEN('Date(s):'))
                                          )))
                 -- 再匹配'Date:'的情况
                 WHEN CHARINDEX('Date:', W_N9R.W_N9_IDSF) > 0 
                      AND LEN(W_N9R.W_N9_IDSF) > CHARINDEX('Date:', W_N9R.W_N9_IDSF) + LEN('Date:')
                      AND CHARINDEX('Time', W_N9R.W_N9_IDSF, CHARINDEX('Date:', W_N9R.W_N9_IDSF) + LEN('Date:')) > 0
                 THEN RTRIM(LTRIM(SUBSTRING(W_N9R.W_N9_IDSF, 
                                           CHARINDEX('Date:', W_N9R.W_N9_IDSF) + LEN('Date:'),
                                           CHARINDEX('Time', W_N9R.W_N9_IDSF, CHARINDEX('Date:', W_N9R.W_N9_IDSF) + LEN('Date:')) 
                                           - (CHARINDEX('Date:', W_N9R.W_N9_IDSF) + LEN('Date:'))
                                          )))
                 -- 最后匹配'Dates:'的情况
                 WHEN CHARINDEX('Dates:', W_N9R.W_N9_IDSF) > 0 
                      AND LEN(W_N9R.W_N9_IDSF) > CHARINDEX('Dates:', W_N9R.W_N9_IDSF) + LEN('Dates:')
                      AND CHARINDEX('Time', W_N9R.W_N9_IDSF, CHARINDEX('Dates:', W_N9R.W_N9_IDSF) + LEN('Dates:')) > 0
                 THEN RTRIM(LTRIM(SUBSTRING(W_N9R.W_N9_IDSF, 
                                           CHARINDEX('Dates:', W_N9R.W_N9_IDSF) + LEN('Dates:'),
                                           CHARINDEX('Time', W_N9R.W_N9_IDSF, CHARINDEX('Dates:', W_N9R.W_N9_IDSF) + LEN('Dates:')) 
                                           - (CHARINDEX('Dates:', W_N9R.W_N9_IDSF) + LEN('Dates:'))
                                          )))
                 -- 无匹配返回空字符串
                 ELSE ''
             END AS VARCHAR(200)
         ) AS KOX
    ) AS SubQueryAlias -- 替换为原查询中子查询的实际别名
    -- ... 其他更多关联逻辑

说明 #

• 代码完全复刻原函数@format_id=1时的执行顺序和逻辑，保证输出结果和原函数一致
• 若后续需要使用@format_id=2的逻辑，只需将CASE块替换为原函数中对应Time相关的匹配规则即可
• 保持原查询其他部分不变，直接替换函数调用区域即可正常运行

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