July 1, 2016 at 6:23 am
I regularly have to mass update customers address areas when the postcode lookups for those areas are altered. e.g the lookup table can be something like below.
I usually do this as a number of runs. first by first normalising the Address postcodes to just have 1 space, then performing the update using a charindex match upto the first space of the Address postcode with the postcode on the lookup file. Then a left$ check to pick up e.g. TA1 2 or GL54 2 but this has to be done on multiple runs because of the number of characters in the lookup. Can you suggest a better pattern match that would pick up all the different postcode lookup styles?
Area Code Related Postcode or partial code
15 TA1 2
16 TA1 3
17 TA1 4
18 GL53
19 GL52
20 TQ
21 TQ 1
22 GL54 2
July 1, 2016 at 6:38 am
gkrs (7/1/2016)
I regularly have to mass update customers address areas when the postcode lookups for those areas are altered. e.g the lookup table can be something like below.I usually do this as a number of runs. first by first normalising the Address postcodes to just have 1 space, then performing the update using a charindex match upto the first space of the Address postcode with the postcode on the lookup file. Then a left$ check to pick up e.g. TA1 2 or GL54 2 but this has to be done on multiple runs because of the number of characters in the lookup. Can you suggest a better pattern match that would pick up all the different postcode lookup styles?
Area Code Related Postcode or partial code
15 TA1 2
16 TA1 3
17 TA1 4
18 GL53
19 GL52
20 TQ
21 TQ 1
22 GL54 2
I'm guessing that you're trying to collect the outward part of the postcode i.e. the first set of characters? If that's the case are you aware that some parts of London have a format like E1W 2VB for example?
I've done something like this and posted it on here before but I can't find it! I'll have look and see what I can dig up.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
July 1, 2016 at 6:50 am
There's a nice postcode validator from Peso / Peter Larsson at http://weblogs.sqlteam.com/peterl/archive/2008/08/13/Validate-UK-postcode.aspx
Thomas Rushton
blog: https://thelonedba.wordpress.com
July 1, 2016 at 6:51 am
BWFC (7/1/2016)
gkrs (7/1/2016)
I regularly have to mass update customers address areas when the postcode lookups for those areas are altered. e.g the lookup table can be something like below.I usually do this as a number of runs. first by first normalising the Address postcodes to just have 1 space, then performing the update using a charindex match upto the first space of the Address postcode with the postcode on the lookup file. Then a left$ check to pick up e.g. TA1 2 or GL54 2 but this has to be done on multiple runs because of the number of characters in the lookup. Can you suggest a better pattern match that would pick up all the different postcode lookup styles?
Area Code Related Postcode or partial code
15 TA1 2
16 TA1 3
17 TA1 4
18 GL53
19 GL52
20 TQ
21 TQ 1
22 GL54 2
I'm guessing that you're trying to collect the outward part of the postcode i.e. the first set of characters? If that's the case are you aware that some parts of London have a format like E1W 2VB for example?
I've done something like this and posted it on here before but I can't find it! I'll have look and see what I can dig up.
It helps if you know that there are a fixed number of formats, which you can find here.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 1, 2016 at 6:53 am
thanks for reply, yep so taking E1W 2VB as an example the lookup table might have
01 E1W 2
02 E1W 3
03 E1W 4
04 E1
05 E2W
So it would need to pickup the area code 01. This is just an arbitrary code that relates to a delivery area
July 1, 2016 at 7:04 am
Does each postcode have its own area code? How do different parts of the same area relate? For example does BB4 have the same area code as BB4 5? This will be a lot easier if you post some sample data for us.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
July 1, 2016 at 7:42 am
Not always as this relates to data on all of our customers databases. So one customer may have BB4 with the same code as BB4 5 but another could have different codes for both. So the data isn't uniform which makes it a trickier exercise. They can perform the lookups themselves against customer addresses but they have to do it for each one which is laborious so they usually get us to perform the task.
July 1, 2016 at 7:48 am
Yipes...
So even though BB4 5 is part of BB4 they may have different codes in the same database?
Could you split out the different customers' formats and codes into different lookup tables? That would make things easier. If everything in each table was formatted the same, each table could have its own query.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
July 1, 2016 at 8:38 am
Yep I know what you mean, the dbase design/input validation could be a lot better. Thank-you for all the replies. The following LIKE seems to work in a lot of instances so I will try to refine this as I go.
update P
set acode = isnull((
select top 1 acode from cArealookup(nolock) where
(select alk.PostCode from Address alk (nolock)
where alk.Account = P.Account and alk.DelCode = P.DelCode)
LIKE cAreaLookupString + '%')
, p.code)
from aDeliveryDetails P
July 1, 2016 at 9:10 am
gkrs (7/1/2016)
Yep I know what you mean, the dbase design/input validation could be a lot better. Thank-you for all the replies. The following LIKE seems to work in a lot of instances so I will try to refine this as I go.update P
set acode = isnull((
select top 1 acode from cArealookup(nolock) where
(select alk.PostCode from Address alk (nolock)
where alk.Account = P.Account and alk.DelCode = P.DelCode)
LIKE cAreaLookupString + '%')
, p.code)
from aDeliveryDetails P
You might find it easier to manipulate a more conventional style:
UPDATE p
SET acode = isnull(x.acode, p.code)
FROM aDeliveryDetails p
CROSS APPLY (
SELECT TOP(1) acode
FROM cArealookup al
INNER JOIN [Address] alk
ON alk.PostCode LIKE al.cAreaLookupString + '%'
WHERE alk.Account = P.Account
AND alk.DelCode = P.DelCode
ORDER BY *something*
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply