sql - 在多列中搜索多个包含条件

Question

我正在尝试编写一些 SQL 来查询银行对账单表，如果 5 个银行对账单描述列中的任何一个包含“SURPLUS CASH”、“CHQ IN AT”或“，我想将一个列填充为“排除”电荷'

我目前的尝试是

select statement_no
      ,bank_text_1
      ,bank_text_2
      ,bank_text_3
      ,bank_text_4
      ,bank_text_5
      ,amount
      ,case
           when bank_text_1 like '%SURPLUS CASH%'
                or bank_text_1 like '%CHQ IN AT%'
                or bank_text_1 like '%ELECTR CHARGE%'
                or bank_text_2 like '%SURPLUS CASH%'
                or bank_text_2 like '%CHQ IN AT%'
                or bank_text_2 like '%ELECTR CHARGE%'
                or bank_text_3 like '%SURPLUS CASH%'
                or bank_text_3 like '%CHQ IN AT%'
                or bank_text_3 like '%ELECTR CHARGE%'
                or bank_text_4 like '%SURPLUS CASH%'
                or bank_text_4 like '%CHQ IN AT%'
                or bank_text_4 like '%ELECTR CHARGE%'
                or bank_text_5 like '%SURPLUS CASH%'
                or bank_text_5 like '%CHQ IN AT%'
                or bank_text_5 like '%ELECTR CHARGE%'
               then 'Exclude'
           else ''
       end as checker;

很乱，看起来效率不高。有人有更好的建议吗？

非常感谢

示例结果数据:

Statement_no | bank_text_1 | bank_text_2         | bank_text_3    | bank_text_4 | bank_text_5  | amount | checker
0001         | SURPLUS CASH|                     |                |             |              |125.00  |Exclude
0002         | M THOMAS    |TRAINING FEE         |CHQ IN AT 100217|             |              |470.00  |Exclude
0003         |SWALEC       |Training Centre One  |Abergaveny      |             |Electr Charges|700.00  |Exlcude
0004         |M Thomas     |Training Centre Hire |                |             |              |850.00  |  

Answer 1

一个选项是交叉应用

Select statement_no
     , bank_text_1
     , bank_text_2
     , bank_text_3
     , bank_text_4
     , bank_text_5
     , amount  
     , B.Checker
 From  YourTable A
 Cross Apply (Select Checker = case when count(*)>0 then 'Exclude' else '' end
               From ( values (A.bank_text_1)
                    ,(A.bank_text_2)
                    ,(A.bank_text_3)
                    ,(A.bank_text_4)
                    ,(A.bank_text_5) ) C1 (Value)
                Where charindex('SURPLUS CASH',Value)
                     +charindex('CHQ IN AT',Value)
                     +charindex('ELECTR CHARGE',Value) 
                     >0
             ) B

返回