CODEWITHSUNDEEP
sql-server-2008
user2726975
user2726975

Reputation: 1353

sql server rank using keywords

I have a table which contains Bank ID (not unique) and Bank names in a table. If the bank name contains keyword 'bank' I want to rank it one and if it has 'financial' rank it two and 'Trust' gets a rank of three. 

 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')

The output should look like

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

For bankid =3, since bankname has both keywords bank and trust, I get 3 rows, 'People''s bank trust' with rank=1 'People''s financial service'with rank=2 'People''s bank trust' with rank=3. How can i avoid this

Thanks MR

Upvotes: 0

Views: 39

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

Use a CASE expression:

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

Fortunately SQL Server supports character ranges in its LIKE expressions, making it easy to match the keywords regardless of whether the first letter be capitalized or not.

Upvotes: 1

Related Questions