如何修改DB2 SQL语句仅获取Lookup String 41031052-1的不同配置

Lookup String为41031052-1，现有DB2 SQL语句查询出多个以此字符串开头的ITEMNO，但仅需获取该字符串本身及其带F的版本（即41031052-1和41031052-1 F），如何修改SQL得到期望结果？

原SQL语句 #

Select Distinct t1.itnbr As itemno
From amflib1.itmrva t1
Join amflib1.whsmst t2 On t2.STID = t1.STID And t2.whid In('100','200','888')
Join amflib1.itembl t3 On t3.itnbr = t1.itnbr And t3.house = t2.whid And t3.planib >= 0
Where t1.itnbr Like '41031052-1%' And t1.itcls <> 'TOOL' Order By 1

当前查询结果 #

ITEMNO
 ---------------
 41031052-1     
 41031052-1    F
 41031052-10    
 41031052-11    
 41031052-11   F
 41031052-12    
 41031052-12   F
 41031052-13    
 41031052-13   F
 41031052-14    
 41031052-14   F
 41031052-15    
 41031052-15   F
 41031052-17    
 41031052-17   F
 41031052-19    
 41031052-19   F

期望查询结果 #

ITEMNO
 ---------------
 41031052-1     
 41031052-1    F

修改方案 #

方法1：精确匹配（格式固定时首选） #

直接指定需要匹配的两个值，适合ITEMNO的带F版本格式完全固定的场景：

Select Distinct t1.itnbr As itemno
From amflib1.itmrva t1
Join amflib1.whsmst t2 On t2.STID = t1.STID And t2.whid In('100','200','888')
Join amflib1.itembl t3 On t3.itnbr = t1.itnbr And t3.house = t2.whid And t3.planib >= 0
Where (t1.itnbr = '41031052-1' OR t1.itnbr = '41031052-1    F') 
  And t1.itcls <> 'TOOL' 
Order By 1

方法2：正则表达式匹配（灵活处理空格） #

如果带F版本的空格数量不固定，使用DB2的正则表达式匹配，确保仅匹配原字符串本身或原字符串后加任意空格和F的情况：

Select Distinct t1.itnbr As itemno
From amflib1.itmrva t1
Join amflib1.whsmst t2 On t2.STID = t1.STID And t2.whid In('100','200','888')
Join amflib1.itembl t3 On t3.itnbr = t1.itnbr And t3.house = t2.whid And t3.planib >= 0
Where (t1.itnbr REGEXP '^41031052-1(\\s*F)?$') 
  And t1.itcls <> 'TOOL' 
Order By 1

正则说明：^匹配字符串开头，\\s*匹配0个或多个空格，F?匹配0个或1个F，$匹配字符串结尾，确保不会匹配到41031052-10这类后续带数字的情况。

方法3：字符串截取判断（兼容旧版DB2） #

如果DB2版本不支持正则，通过截取字符串进行判断：

Select Distinct t1.itnbr As itemno
From amflib1.itmrva t1
Join amflib1.whsmst t2 On t2.STID = t1.STID And t2.whid In('100','200','888')
Join amflib1.itembl t3 On t3.itnbr = t1.itnbr And t3.house = t2.whid And t3.planib >= 0
Where LEFT(t1.itnbr, 9) = '41031052-1' 
  AND (LTRIM(RIGHT(t1.itnbr, LENGTH(t1.itnbr)-9)) = '' OR LTRIM(RIGHT(t1.itnbr, LENGTH(t1.itnbr)-9)) = 'F')
  And t1.itcls <> 'TOOL' 
Order By 1

逻辑：先取ITEMNO前9位（41031052-1的长度）匹配Lookup String，再判断剩余部分去掉空格后要么为空，要么是F。

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