今天写一个用于标准化取数脚本的时候，涉及到剔除某些编码段的问题，即多个模糊查询的问题，如下

 and cm.inventory_item_id not in
       (select m2.inventory_item_id
          from inv.mtl_system_items_b m2
         where m2.organization_id = 1036
           and m2.segment1 like
               ('5328%', '5329%', '5330%', '5331%', '5332%', '5333%', '5334%',
                '5344%', '5345%', '5346%', '5347%', '5348%', '5349%', '5358%',
                '5359%', '5360%', '5361%', '5362%', '5363%', '5364%', '5374%',
                '5375%', '5376%', '5377%', '5378%', '5379%', '5380%'))

执行时，报错。估计是like的用法错误，于是将like改成了in，执行时未报错。理所当然地以为是正确的。（印象中好像是不能这样用in的）

 and cm.inventory_item_id not in
       (select m2.inventory_item_id
          from inv.mtl_system_items_b m2
         where m2.organization_id = 1036
           and m2.segment1 in

               ('5328%', '5329%', '5330%', '5331%', '5332%', '5333%', '5334%',
                '5344%', '5345%', '5346%', '5347%', '5348%', '5349%', '5358%',
                '5359%', '5360%', '5361%', '5362%', '5363%', '5364%', '5374%',
                '5375%', '5376%', '5377%', '5378%', '5379%', '5380%'))

请教Happy后，发现之前两个种用法都是错误的！

如果用like，应该这样写：

and (m2.segment1 like '5328%' or m2.segment1 like '5329%' or m2.segment1 like '5330%'......)

如果用in，可以借用substr函数：

and substr(m2.segment1,1,4) in
               ('5328', '5329', '5330', '5331', '5332', '5333', '5334',
                '5344', '5345', '5346', '5347', '5348', '5349', '5358',
                '5359', '5360', '5361', '5362', '5363', '5364', '5374',
                '5375', '5376', '5377', '5378', '5379', '5380')

注：本文转自菜鸟笔记-http://blog.163.com/xin_er_yes/blog/static/1762154742011424102846425/