Extract part of Zipcode from full address string

Transact-SQL
1

Hi,
I have a field with full address (1234 StreetName, #222, City, State, 880770000). I want the full address and first 5 digits of zipcode. In the database zipcodes could be 5 digits or more (Ex: 88077000 or 88092 or 88009200). Any help will be appreciated.
Thanks!

2

need to know ALL the possible formats that could exist.

What would have been better would have been for you to provide a CREATE TABLE staement and some INSERT statements that populated it with all the sample data you wanted to test, and examples of the output. Then folk here could use your Test Rig to make AND TEST answers for you. Otherwise each individual person is going to have to do that, duplicating what everyone else is doing and ... you will find that people here don't have that time - that's your job :slightly_smiling:

Does the Zipcode ALWAYS come at the end?
Is it always digits, and always 5 or 9 digits?
Is there always a space before it?

If that is true :slightly_smiling: then


; WITH MyAddresses AS
(
	SELECT [Address] = '1234 StreetName, #222, City, State, 123456789'
	UNION ALL SELECT '1234 StreetName, #222, City, State, 12345'
	UNION ALL SELECT 'NoZip StreetName, #222, City, State, xxxx'
)
SELECT	CASE WHEN [Address] LIKE '% [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT([Address], 9)
	     WHEN [Address] LIKE '% [0-9][0-9][0-9][0-9][0-9]' THEN RIGHT([Address], 5)
	     ELSE NULL
	     END AS [ZipCode],
	[Address]
FROM	MyAddresses
WHERE	   [Address] LIKE '% [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
	OR [Address] LIKE '% [0-9][0-9][0-9][0-9][0-9]'
3

You could always do it the hard way:

WITH MyAddresses AS
(
	SELECT [Address] = '1234 StreetName, #222, City, State, 123456789'
	UNION ALL SELECT '1234 StreetName, #222, City, State, 12345'
	UNION ALL SELECT 'NoZip StreetName, #222, City, State, xxxx'
)
SELECT LEFT(REVERSE(LEFT(REVERSE(Address), CHARINDEX(' ', REVERSE(Address))-1)), 5)  
FROM MyAddresses;

Thanks @Kristen for the test data.

4

Thanks! Kristen and djj55 for quick response.
Is there is way to get the complete address and first 5 digits of zipcode in a single string instead of splitting the zipcode. Thanks!

5

Yes, the zipcode always at the end, it could be 5 or more digits and will always have space before zipcode.

Declare @Temp Table(Address VarChar(50))

Insert Into @Temp Values('10235 Aldine Westfield Rd, Dallas TX 770930000')
Insert Into @Temp Values('06047 Dwyer St, Houston TX 77002')
Insert Into @Temp Values('00406 E 31St St, Austin TX 880180000')
Insert Into @Temp Values('03116 Adelia St, Main St NY 66026')
Insert Into @Temp Values('07111 Bonita St, Clay MN 550162811')
Insert Into @Temp Values('17111 Bonita St, Clay MN 5501628112')

Select Address
From @Temp

6

:slightly_smiling:

So ... I read that as "If address ends in 5 or more digits, then truncate to leave only the last 5 digits"

SELECT	LEFT(Address
		, CASE WHEN Address LIKE '% %'
			AND RIGHT(Address, CHARINDEX(' ', REVERSE(Address))-1)
                                NOT LIKE '%[^0-9]%'
			AND CHARINDEX(' ', REVERSE(Address)) > 6	-- Includes the space
		THEN LEN(Address) - CHARINDEX(' ', REVERSE(Address)) + 6
		ELSE LEN(Address)
		END)
FROM	@Temp

Dunno if that is the most efficient though ...

I added these additional edge cases to your data

Insert Into @Temp Values('No Trailing Digits')
Insert Into @Temp Values('NoSpaces')
Insert Into @Temp Values('')
Insert Into @Temp Values(NULL)

(You would be better off storing the address in a structure style - separate columns for each line of the address, and the State / Zipcode etc. so one option would be to PARSE the addresses into a multi-column table. That would then let you find Zipcode easily, including having indexes on Zipcode / State / whatever, and being able to print a Label for an envelop ... and so on ... separate issue though, obviously)

1 Like
7

Thanks You! Kristen