使用关键字的sql服务器级别

Question

我有一个表，它包含一个表中的银行ID (不是唯一的)和银行名称。如果银行名称中包含关键字' bank‘，我想给它排序一次，如果它有’金融‘，排名第二，而’信任‘则排名第三。

 Create table dbo.banknames(id int null,bankname varchar(200) null)
 insert into dbo.bankname(1,'U.S. Trust')
 insert into dbo.bankname(1,'Bank of america')
 insert into dbo.bankname(1,'BOA Financial services')
 insert into dbo.bankname(2,'Citizens trust')
 insert into dbo.bankname(3,'People''s bank trust')
 insert into dbo.bankname(3,'People''s financial service')

输出应该如下所示

Create table dbo.ExpectedOUTPUTbanknames(id int null,bankname varchar(200) null,rank int null)
insert into dbo.ExpectedOUTPUTbankname(1,'U.S. Trust',3)
insert into dbo.ExpectedOUTPUTbankname(1,'Bank of america',1)
insert into dbo.ExpectedOUTPUTbankname(1,'BOA Financial services',2)
insert into dbo.ExpectedOUTPUTbankname(2,'Citizens trust',3)
insert into dbo.ExpectedOUTPUTbankname(3,'People''s bank trust',1)
insert into dbo.ExpectedOUTPUTbankname(3,'People''s financial service',2)

select * 
into dbo.OUTPUTbankname
(
SELECT *,1 as RNK FROM  dbo.banknames
    WHERE (bankname LIKE '%bank%')
    UNION
    SELECT *,1 as RNK FROM  dbo.banknames
    WHERE (bankname LIKE '%financial%')
    UNION
    SELECT *,1 as RNK FROM  dbo.banknames
    WHERE (bankname LIKE '%trust%')
) qrey

对于bankid =3，因为银行名称有关键字银行和信任，所以我得到了3行，与rank=1的“人民的银行信任”和rank=2的“人民的银行信任”和rank=3的“人民的银行信任”。

谢谢先生

Answer 1

使用CASE表达式：

SELECT id,
       bankname,
       CASE WHEN bankname LIKE '%[Bb]ank%'      THEN 1
            WHEN bankname LIKE '%[Ff]inancial%' THEN 2
            WHEN bankname LIKE '%[Tt]rust%'     THEN 3
       END AS rank
FROM dbo.banknames

幸运的是，Server在其LIKE表达式中支持字符范围，因此无论第一个字母是否大写，匹配关键字都很容易。