CODEWITHSUNDEEP
sqlsql-servert-sqlvariablessql-like
RustyHamster
RustyHamster

Reputation: 359

SQL Multiple LIKE Statements

I'm currently working on a report that shows me all postcodes covered by our sales team.

Each team covers over 100 postcodes. What I would like to do is create a report that brings back the clients within the postcode. Currently, my code looks like this.

SELECT * FROM tbl_ClientFile
 WHERE CLNTPOST1 LIKE ('B79%')
  OR CLNTPOST1 LIKE ('BB1%')
  OR CLNTPOST1 LIKE ('BB10%')
  OR CLNTPOST1 LIKE ('BB11%')
  OR CLNTPOST1 LIKE ('BB12%')
  OR CLNTPOST1 LIKE ('BB18%')
  OR CLNTPOST1 LIKE ('BB2%')
  OR CLNTPOST1 LIKE ('BB3%')
  OR CLNTPOST1 LIKE ('BB4%')
  OR CLNTPOST1 LIKE ('BB5%')
  OR CLNTPOST1 LIKE ('BB6%')
  OR CLNTPOST1 LIKE ('BB8%')
  OR CLNTPOST1 LIKE ('BB9%')
  OR CLNTPOST1 LIKE ('BB94%')
  OR CLNTPOST1 LIKE ('BD1%')
  OR CLNTPOST1 LIKE ('BD10%')
  OR CLNTPOST1 LIKE ('BD11%')
  OR CLNTPOST1 LIKE ('BD12%')
  OR CLNTPOST1 LIKE ('BD13%')
  OR CLNTPOST1 LIKE ('BD14%')
  OR CLNTPOST1 LIKE ('BD15%')
  OR CLNTPOST1 LIKE ('BD16%')
  OR CLNTPOST1 LIKE ('BD17%')
  OR CLNTPOST1 LIKE ('BD18%')
  OR CLNTPOST1 LIKE ('BD19%')
  OR CLNTPOST1 LIKE ('BD2%')
  OR CLNTPOST1 LIKE ('BD20%')
  OR CLNTPOST1 LIKE ('BD21%')
  OR CLNTPOST1 LIKE ('BD22%')
  OR CLNTPOST1 LIKE ('BD3%')
  OR CLNTPOST1 LIKE ('BD4%')
  OR CLNTPOST1 LIKE ('BD5%')
  OR CLNTPOST1 LIKE ('BD6%')

What I was hoping for is that there is a faster and easier way of doing this. Any suggestions would be greatly appreciated. Is there a way to create a variable for each sales Team like @SalesTeam1 = SELECT * FROM tbl_ClientFile WHERE POSTCODE1 like '' or like ''

Just fishing for ideas really. Cheers

Upvotes: 11

Views: 60234

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Most of your likes are already covered by other likes. You can use likes with multiple values like this and get the same result:

SELECT * FROM tbl_ClientFile
WHERE 
  CLNTPOST1 LIKE ('B79%')
  OR CLNTPOST1 LIKE ('BB[12345689]%')
  OR CLNTPOST1 LIKE ('BD[1-6]%')

If you check for CLNTPOST1 is like BB1%, then you don't have to check for BB11% OR BB12%

Upvotes: 4

Ananda Kumar Jayaraman
Ananda Kumar Jayaraman

Reputation: 399

WITH CTE AS
(
SELECT VALUE
FROM (
        VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'),
                ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6')
     ) V(VALUE)
)   



SELECT * 
FROM tbl_ClientFile T
WHERE EXISTS ( SELECT TOP 1 1 FROM CTE WHERE T.CLNTPOST1 LIKE CTE.VALUE + '%')

Upvotes: 13

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

One of possible solutions. Create a table Prefix(v varchar(4)) where you insert those values. Then a solution would be:

SELECT * 
FROM tbl_ClientFile cf
JOIN Prefix p on cf.CLNTPOST1 LIKE p.v + '%'

To exclude duplicates if some prefix includes some another prefix like BB1, BB10, BB15...:

SELECT DISTINCT cf.* 
FROM tbl_ClientFile cf
JOIN Prefix p on cf.CLNTPOST1 LIKE p.v + '%'

Upvotes: 6

Related Questions