DB2 SUBSTR函数长度参数未生效问题排查求助

问题描述 #

我编写了一段SQL，用于计算起始位置与结束位置之间的长度，再尝试使用该起始位置和长度提取子串，但SUBSTR函数似乎仅识别起始位置，忽略了长度参数。

原SQL代码 #

Select --PMREF_REFERENCE,IOMSG_RECEIVER, IOMSG_SENDER, IOMSG_FORMAT,    
         posstr(MCONT_DATA,':59:/')  AS "Tag59 St" ,                   
         INSTR(MCONT_DATA, X'0D25', posstr(MCONT_DATA,':59:/')) AS "@",
         (posstr(MCONT_DATA,':59:/')) AS "T59 end",                    
         (INSTR(MCONT_DATA, X'0D25', posstr(MCONT_DATA,':59:/'))       
            - (posstr(MCONT_DATA,':59:/')) ) AS "len" ,                
-- next should start at pos of tatg 59 for length - DOESN'T WORK!      
  SUBSTR(substr(MCONT_DATA, posstr(MCONT_DATA,':59:/') ,               
         (INSTR(MCONT_DATA, X'0D25', posstr(MCONT_DATA,':59:/'))       
            - (posstr(MCONT_DATA,':59:/')) )                           
               ), 1, 13)                                                
--         (INSTR(MCONT_DATA, X'0D25', posstr(MCONT_DATA,':59:/'))     
--            - (posstr(MCONT_DATA,':59:/')))                          
        ) AS "T59 str"                                                 
--                                                                     
      ,  substr(MCONT_DATA,                                            
                posstr(MCONT_DATA,':57A:') , 15) AS "Tag 57" ,         
          substr(MCONT_DATA,                                           
            posstr(MCONT_DATA,':20:') , 20) AS "Tag 20" ,              
         CASE                                                          
       WHEN posstr(MCONT_DATA,':50A:') > 0                             
         THEN substr(MCONT_DATA,                                       
                posstr(MCONT_DATA,':50A:') + 5, 20)                    
       WHEN posstr(MCONT_DATA,':50F:') > 0                             
          THEN substr(MCONT_DATA,                                      
                 posstr(MCONT_DATA,':50F:') + 5, 20)                   
        END                                                            
        , substr(mcont_data,1,550)                                     
FROM RBS.TPPIOMSG A, RBS.TPPMCONT B, RBS.TPPPMREF C    etc...     

原执行结果 #

> Tag59 St            @      T59 end          len  T59 str        Tag 57    
> -------+---------+---------+---------+---------+---------+---------+-------
>                                                                           
> -------+---------+---------+---------+---------+---------+---------+-------
>      395          408          395           13  :59:/20276668  :57A:RBOSG
>      407          420          407           13  :59:/00650006  :57A:RBOSG
>      388          401          388           13  :59:/32882288  :57A:RBOSG
>      409          422          409           13  :59:/10011751  :57A:RBOSG
>      400          413          400           13  :59:/12022717  :57A:RBOSG
>      378          391          378           13  :59:/51435616  :57A:RBOSG
>      407          420          407           13  :59:/00284446  :57A:RBOSG

修改后的SQL代码（长度参数改为表达式） #

Select --PMREF_REFERENCE,IOMSG_RECEIVER, IOMSG_SENDER, IOMSG_FORMAT,       
         posstr(MCONT_DATA,':59:/')  AS "Tag59 St" ,                      
         INSTR(MCONT_DATA, X'0D25', posstr(MCONT_DATA,':59:/')) AS "@",   
         (posstr(MCONT_DATA,':59:/')) AS "T59 end",                       
         (INSTR(MCONT_DATA, X'0D25', posstr(MCONT_DATA,':59:/'))          
            - (posstr(MCONT_DATA,':59:/')) ) AS "len" ,                   
-- next should start at pos of tatg 59 for length - DOESN'T WORK!         
  SUBSTR(substr(MCONT_DATA, posstr(MCONT_DATA,':59:/') ,                  
         (INSTR(MCONT_DATA, X'0D25', posstr(MCONT_DATA,':59:/'))          
            - (posstr(MCONT_DATA,':59:/')) )                              
--             ), 1, 13                                                   
      ),1, (INSTR(MCONT_DATA, X'0D25', posstr(MCONT_DATA,':59:/'))        
              - (posstr(MCONT_DATA,':59:/')))                             
        ) AS "T59 str"                                                    
                                                                          
      ,  substr(MCONT_DATA,                                               
                posstr(MCONT_DATA,':57A:') , 15) AS "Tag 57" ,            
          substr(MCONT_DATA,                                              
            posstr(MCONT_DATA,':20:') , 20) AS "Tag 20" ,                 
         CASE                                                             
       WHEN posstr(MCONT_DATA,':50A:') > 0                                
         THEN substr(MCONT_DATA,                                          
                posstr(MCONT_DATA,':50A:') + 5, 20)                       
       WHEN posstr(MCONT_DATA,':50F:') > 0                                
          THEN substr(MCONT_DATA,                                         
                 posstr(MCONT_DATA,':50F:') + 5, 20)                      
        END                                                               
        , substr(mcont_data,1,550)                                        
FROM RBS.TPPIOMSG A, RBS.TPPMCONT B, RBS.TPPPMREF C   etc..     

修改后的执行结果 #

Tag59 St            @      T59 end          len  T59 str                     
-------+---------+---------+---------+---------+---------+---------+---------+
                                                                              
-------+---------+---------+---------+---------+---------+---------+---------+
      395          408          395           13  :59:/20276668               
      407          420          407           13  :59:/00650006               
      388          401          388           13  :59:/32882288               
the T59 str is a 4k length! 

另一种错误写法 #

substr(MCONT_DATA, posstr(MCONT_DATA,':59:/') ,                  
         (INSTR(MCONT_DATA, X'0D25', posstr(MCONT_DATA,':59:/')) - (posstr(MCONT_DATA,':59:/')) )                                                                                
          , (INSTR(MCONT_DATA, X'0D25', posstr(MCONT_DATA,':59:/'))      
              - (posstr(MCONT_DATA,':59:/')))
        ) AS "T59 str" 

问题原因及解决方法 #

1. 残留标记符号引发语法解析错误 #

你在代码中添加的**标记（用于突出修改位置）如果未删除，会破坏SQL语法结构，导致数据库无法正确解析SUBSTR的参数，进而忽略长度参数，直接截取到字符串末尾，出现4k长度的结果。

2. 冗余的嵌套SUBSTR #

完全不需要嵌套两层SUBSTR，一层SUBSTR即可完成需求：从Tag59 St位置开始，截取长度为len的子串。嵌套写法不仅冗余，还容易引发参数解析错误。

3. 错误传递4个参数给SUBSTR #

在最后一种写法中，你给SUBSTR传递了4个参数，但SUBSTR函数仅接受3个参数（字符串、起始位置、长度），多余的参数会被数据库忽略或导致解析错误，使得长度参数失效。

正确写法 #

SUBSTR(MCONT_DATA, posstr(MCONT_DATA,':59:/'), 
       INSTR(MCONT_DATA, X'0D25', posstr(MCONT_DATA,':59:/')) - posstr(MCONT_DATA,':59:/')
) AS "T59 str"

直接使用一层SUBSTR，传入正确的三个参数：原字符串、起始位置、计算得到的长度，即可正确提取目标子串。

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