strip out characters and build zip codes cleanly in sql
Posted on 2014-07-24
Hi I want to structure some data cleaning routines
I have some data (nvarchar(max) in a table called zips I want to strip out the numbers from ZIP into a separate column ZIP2 see below. Not all zips start with letters (I only want the number values) and some have - in them
I will rebuild the zip using a concatenate stated later but I need to get ZIP2 without any letters and left aligned
What is the UPDATE ZIP2 statement to achieve this?
ie update dbo.zips
set zip2 = cast? zip as int?????
UT 84405 84405
VA 23320 23320
DC 20002-4241 20002-4241
IL 60064-3500 60064-3500
I have sql2012