sql server - SQL Multiple LIKE Statements

Question

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

Each team covers over 100 post codes. What i would like to do is create a report that brings back the clients within the post code. 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

Answer 1

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