Removing Postcodes from Address1 column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Removing Postcodes from Address1 column

Author

Topic

NickC
Yak Posting Veteran

68 Posts

Posted - 2010-09-07 : 05:39:05

Hello

I have been tasked with moving the postcodes from part of one column to another.

Basically we have two columns (one being address1 and another being postcode) however some bright sparks decided to put the postcode onto the address1 columns for some of the rows (not all) and I'm strugglign to workout how to fix it, someone suggested pattern matching to capture them however I have no idea how to do that, anyone able to help or done something similar?

Thanks :D

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-07 : 05:40:23

Please post some sample data...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

NickC
Yak Posting Veteran

68 Posts

Posted - 2010-09-07 : 05:55:54

An example might be

address1 address2 address3 address4 postalcode
150 Westbury Road Southend Essex SS2 4DN Correct
150 Westbury Road Southend EssexSS24DN Incorrect
150 Westbury Road Southend SS2 4DN Correct
150 Westbury Road Southend SS2 4DN Incorrect
150 Westbury Road SouthendSS24DN Essex Incorrect

As you can see for examples postcodes are joined on to the address columns (address1,address2,address3,address4) they are incorrect

Hope this helps,

Thanks

NickC
Yak Posting Veteran

68 Posts

Posted - 2010-09-07 : 05:57:30

Sorry example looked better before posting,

basically if ne postcode is in one of the 4 address columns, i need it moving to postal_code column, I think I need to patindex to find postcodes then do a move of some sort?

NickC
Yak Posting Veteran

68 Posts

Posted - 2010-09-07 : 05:58:27

This code helps indentify postalcodes (although helpful for other tasks, not necessary for this one, but it shows valid UK postcodes to help - altho can't get it to work :( as I'm a newbie)

CREATE FUNCTION dbo.fnValidatePostCodeUK
(
@PostCode VARCHAR(8)
)
RETURNS BIT
AS
BEGIN
RETURN CASE
-- Special case GIR 0AA
WHEN @PostCode LIKE 'GIR 0AA' THEN 1
-- Current postcode prefixes
WHEN LEFT(@Postcode, 2) NOT IN ('AB', 'AL', 'BA', 'BB', 'BD', 'BH', 'BL', 'BN', 'BR', 'BS', 'BT', 'CA', 'CB', 'CF', 'CH', 'CM', 'CO', 'CR', 'CT', 'CV', 'CW', 'DA', 'DD', 'DE', 'DG', 'DH', 'DL', 'DN', 'DT', 'DY', 'EC', 'EH', 'EN', 'EX', 'FK', 'FY', 'GL', 'GU', 'GY', 'HA', 'HD', 'HG', 'HP', 'HR', 'HS', 'HU', 'HX', 'IG', 'IM', 'IP', 'IV', 'JE', 'KA', 'KT', 'KW', 'KY', 'L', 'LA', 'LD', 'LE', 'LL', 'LN', 'LS', 'LU', 'ME', 'MK', 'ML', 'NE', 'NG', 'NN', 'NP', 'NR', 'NW', 'OL', 'OX', 'PA', 'PE', 'PH', 'PL', 'PO', 'PR', 'RG', 'RH', 'RM', 'SA', 'SE', 'SG', 'SK', 'SL', 'SM', 'SN', 'SO', 'SP', 'SR', 'SS', 'ST', 'SW', 'SY', 'TA', 'TD', 'TF', 'TN', 'TQ', 'TR', 'TS', 'TW', 'UB', 'WA', 'WC', 'WD', 'WF', 'WN', 'WR', 'WS', 'WV', 'YO', 'ZE')
OR WHEN LEFT(@Postcode, 1) NOT IN ('B', 'E', 'G', 'L', 'N', 'S', 'W') THEN 0
-- AANN NAA
WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1
-- AANA NAA
WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1
-- ANN NAA
WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1
-- AAN NAA
WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1
-- ANA NAA
WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1
-- AN NAA
WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1
-- Not a valid postcode
ELSE 0
END
END

NickC
Yak Posting Veteran

68 Posts

Posted - 2010-09-07 : 08:30:07

anyone able to help :/

Kristen
Test

22859 Posts

Posted - 2010-09-07 : 08:53:12

quote:
Originally posted by NickC

Sorry example looked better before posting,

Put [code] tags around it:


address1             address2                address3              address4               postalcode
150 Westbury Road    Southend                Essex                                        SS2 4DN     Correct
150 Westbury Road    Southend                EssexSS24DN                                              Incorrect
150                  Westbury Road           Southend                                     SS2 4DN     Correct
150                  Westbury Road           Southend              SS2 4DN                            Incorrect
150 Westbury Road    SouthendSS24DN          Essex                                                    Incorrect

NickC
Yak Posting Veteran

68 Posts

Posted - 2010-09-07 : 08:55:31

Thank Kristen,

Atleast people can see what I'm trying to do now....

anotehr issue is that sometimes the postcode is like this

Address4
ss24dn southend

Kristen
Test

22859 Posts

Posted - 2010-09-07 : 08:57:19

Postcodes don;t have very easy-to-determine characteristics.

Having a postalcode column that is blank/NULL may help - e.g.


WHERE postalcode IS NULL
      AND
      (
             address4 LIKE '[A-Z][A-Z][0-9] [A-Z][A-Z][0-9]'
          OR address4 LIKE '[A-Z][A-Z][0-9][0-9] [A-Z][A-Z][0-9]'
          OR address4 LIKE '[A-Z][0-9][A-Z] [A-Z][A-Z][0-9]'
          ...
      )

could be used as there WHERE clause for an UPDATE to move address4 to postalcode. But catching "EssexSS24DN" will be much harder

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-07 : 10:06:09

Yay! Someone actually uses my function here http://weblogs.sqlteam.com/peterl/archive/2008/08/13/Validate-UK-postcode.aspx

N 56°04'39.26"
E 12°55'05.63"

NickC
Yak Posting Veteran

68 Posts

Posted - 2010-09-07 : 10:41:10

Yes Peso, it was very good, Ive used it to identify the issues with postcodes, now I'm trying to find a way to just get postcode from address4 to change postchecked from 'no postcode to postcode'

I think I'm on the right sort of lines with this because it does pull the psotcode out however its determining the correct postcode now as obviously a1 1aa is picked up if true postcode is aa1 1aa, anyone who can help would be amazing!!!

anyway cheers peso, keep creating them functions your keeping me employed at moment (barely - junior role :/)

select *,

--patindex('%[a-z][0-9][ ][0-9][a-z][a-z]%',address4) as [A9 9AA],
substring(address4,patindex('%[a-z][0-9][ ][0-9][a-z][a-z]%',address4),6) as [A9 9AA],
--patindex('%[a-z][0-9][0-9][ ][0-9][a-z][a-z]%',address4) as [A99 9AA],
substring(address4,patindex('%[a-z][0-9][0-9][ ][0-9][a-z][a-z]%',address4),7) as [A99 9AA],
--patindex('%[a-z][a-z][0-9][ ][0-9][a-z][a-z]%',address4) as [AA9 9AA],
substring(address4,patindex('%[a-z][a-z][0-9][ ][0-9][a-z][a-z]%',address4), 8) as [AA9 9AA],
--patindex('%[a-z][a-z][0-9][0-9][ ][0-9][a-z][a-z]%',address4) as [AA99 9AA],
substring(address4,patindex('%[a-z][a-z][0-9][0-9][ ][0-9][a-z][a-z]%',address4),8) as [AA99 9AA],
--patindex('%[a-z][0-9][a-z][ ][0-9][a-z][a-z]%',address4) as [A9A 9AA],
substring(address4,patindex('%[a-z][0-9][a-z][ ][0-9][a-z][a-z]%',address4),7) as [A9A 9AA],
--patindex('%[a-z][a-z][0-9][a-z][0-9][ ][0-9][a-z][a-z]%',address4) as [AA9A 9AA],
substring(address4,patindex('%[a-z][a-z][0-9][a-z][0-9][ ][0-9][a-z][a-z]%',address4),8) as [AA9A 9AA],
--patindex('%[a-z][0-9][0-9][a-z][a-z]%',address4) as [A99AA],
substring(address4,patindex('%[a-z][0-9][0-9][a-z][a-z]%',address4),5) as [A99AA],
--patindex('%[a-z][0-9][0-9][0-9][a-z][a-z]%',address4) as [A999AA],
substring(address4,patindex('%[a-z][0-9][0-9][0-9][a-z][a-z]%',address4),6) as [A999AA],
--patindex('%[a-z][a-z][0-9][0-9][a-z][a-z]%',address4) as [AA99AA],
substring(address4,patindex('%[a-z][a-z][0-9][0-9][a-z][a-z]%',address4),6) as [AA99AA],
--patindex('%[a-z][a-z][0-9][0-9][0-9][a-z][a-z]%',address4) as [AA999AA],
substring(address4,patindex('%[a-z][a-z][0-9][0-9][0-9][a-z][a-z]%',address4),7) as [AA999AA],
--patindex('%[a-z][0-9][a-z][0-9][a-z][a-z]%',address4) as [A9A9AA],
substring(address4,patindex('%[a-z][0-9][a-z][0-9][a-z][a-z]%',address4),6) as [A9A9AA],
--patindex('%[a-z][a-z][0-9][a-z][0-9][0-9][a-z][a-z]%',address4) as [AA9A9AA]
substring(address4,patindex('%[a-z][0-9][a-z][0-9][a-z][a-z]%',address4),7) as [AA9A9AA]

from new_address_2

where [post code checker] = 0

Kristen
Test

22859 Posts

Posted - 2010-09-07 : 13:48:11

If it was me I would just move anything that looked like a PostCode to the Postalcode column. The RegEx is not foolproof anyway - IP99 1AB is probably validated by the function, but I doubt the IPnn range goes about 30 or 40.

We use PostCodeAnywhere for validation of postcodes. I haven't looked at their services for ages, but it might be that they can validate the addresses and sort out any part of the address that is goofy (such as "post code appended to county name"). Not "free" though ... but would have the advantage of all addresses being PAF-validated/cleaned-up

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources