Extracting zip code from an open text address field using T-SQL

Sometime we come across a situation where we need the ZIP code of an address but the raw data from operational systems is coming as an address collected from an open field i.e. ZIP code is part of the address itself. With such an open field provision you can not expect a consistency that ZIP code will always be the last five digits in the address. End user may write it anywhere in the address so let us see how can we extract the zip code from such open fields using SQL in an easy way –

Let us first cook some dummy records representing the address from an open field –

DECLARE @Temp TABLE
(
	AddressField VARCHAR(255)
)

INSERT INTO @Temp VALUES
  ('06000 Darul Aman Malaysia Address')
, ('Brooklyn Bridge 12345 New York')
, ('Another Address UK 56780')
, ('Address with no zip code')
, ('Random number 1234 XYZ')

SELECT * FROM @Temp

Sample data is showing all the possible scenarios like zip code appearing at the beginning, somewhere in the middle, at the end, no zip code at all or some other numbers but not consecutive five digits –

The idea here is to find consecutive five digits from the field and consider that as a zip code which can easily be found using PATINDEX function in SQL Server –

SELECT AddressField
	, SUBSTRING(AddressField, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AddressField),5) AS ZipCode
FROM @Temp

It gives you the following output –

Executing the previous query, you can see that Zip codes comes out easily but with a catch that it doesn’t give you an appropriate result when there is no match for your pattern of zip codes so let us just amend the query to handle this and get a perfect result –

SELECT AddressField
	, CASE WHEN PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AddressField) > 0 
			THEN SUBSTRING(AddressField, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AddressField),5) 
			ELSE NULL END
		AS ZipCode
FROM @Temp

It gives you the desired output that if there is no zip code then simply return NULL –

However, there can be more complex scenarios in real life when there are other consecutive five digits numbers present in the address field which are not supposed to be considered as zip codes but for most of the records this approach can work.

Let’s conclude this post with a consolidated script used for the demonstration –

DECLARE @Temp TABLE
(
	AddressField VARCHAR(255)
)

INSERT INTO @Temp VALUES
  ('06000 Darul Aman Malaysia Address')
, ('Brooklyn Bridge 12345 New York')
, ('Another Address UK 56780')
, ('Address with no zip code')
, ('Random number 1234 xyz')

--SELECT * FROM @Temp

--SELECT AddressField
--	, SUBSTRING(AddressField, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AddressField),5) AS ZipCode
--FROM @Temp

SELECT AddressField
	, CASE WHEN PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AddressField) > 0 
			THEN SUBSTRING(AddressField, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', AddressField),5) 
			ELSE NULL END
		AS ZipCode
FROM @Temp

Many thanks for reading!