Geek Answers Handbook

SQL plural LIKE messages

I am currently working on a report that shows me all the postal codes covered by our sales team.

Each team covers more than 100 postal codes. What I would like to do is create a report that returns customers to the postal code. Currently my code is as follows.

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 a faster and easier way to do this. Any suggestions would be appreciated. Is there a way to create a variable for each sales team, for example@SalesTeam1 = SELECT * FROM tbl_ClientFile WHERE POSTCODE1 like '' or like ''

Just fish for ideas. Greetings

+4
source share
3 answers

With CTE AS
(
CHOOSE VALUE
 (       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 + '%')

+1

. Prefix(v varchar(4)), . :

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

, - BB1, BB10, BB15...:

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

Most of your likes are already covered by other loved ones. You can use sympathies with several 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 CLNTPOST1 as BB1%, then you do not need to check BB11% or BB12%

+1
source

More articles:


All Articles