Solved

strip out characters and build zip codes cleanly in sql

Posted on 2014-07-24
6
188 Views
Last Modified: 2014-08-08
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?????

ZIP                                               ZIP2    
UT 84405                                  84405
VA 23320                                  23320
DC 20002-4241                        20002-4241
85717                                       85717
IL 60064-3500                          60064-3500


I have sql2012
0
Comment
Question by:Chris Michalczuk
6 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40218642
update YourTable
set zip2 = case when len(zip) > 0 and PATINDEX('%[0-9]%', zip) > 1
                   then substring(zip,PATINDEX('%[0-9]%', zip),len(zip))
                else zip
           end
from Yourtable


select *
from YourTable

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40218653
you can create a function like shown here to remove all non-numerical and non "-":
http://www.experts-exchange.com/Programming/Microsoft_Development/Q_27682377.html

CREATE FUNCTION dbo.udf_GetNumericOnly (@Value varchar(max))

RETURNS varchar(max)  

AS  

BEGIN 

Declare @Pos tinyint,
	@Char char(1)

Set @Value = REPLACE(@Value, ' ', '')		-- Strip all spaces
Set @Pos = LEN(@Value)			-- Give some non-zero value
While @Pos > 0
    Begin
	Set @Pos = PATINDEX('%[^0-9-]%', @Value)		-- This will remove all the non-numeric characters (the spaces have alaready been removed with the REPLACE)
	If @Pos > 0 
	   Begin
		Set @Char = SUBSTRING(@Value, @Pos, 1)		-- Non numeric character
		Set @Value = REPLACE(@Value, @Char, '')
	   End
    End

Return @Value

END
                                          

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40218654
and your update becomes:
update yourtable set zip2 = dbo.udf_GetNumericOnly (zip)
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 13

Expert Comment

by:Molnar István
ID: 40218656
hi,

you can try and query just the zip codes, using regular expression
for using regex in MsSql you can create your own regex functions and use that to query:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27205

you can add more features to sql:
http://www.sqlsharp.com/features/

or use the patindex:
ex:
SELECT PATINDEX('\d{5}-?(\d{4})?',ZIPCOLUMN)
FROM ZIPTABLE

hope it helps
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40219851
UPDATE t
SET
    zip2 = SUBSTRING(zip, PATINDEX('%[0-9]%', zip + '0'), 100)
FROM dbo.tablename t
--WHERE zip2 IS NULL OR zip2 = ''


Sample test:

select
    zip,
    zip2 = SUBSTRING(zip, PATINDEX('%[0-9]%', zip + '0'), 100)
from (
    select 'UT 84405' as zip union all
    select 'VA 23320' union all
    select 'DC 20002-4241' union all
    select '85717' union all
    select 'IL 60064-3500' union all
    select 'TX'
) as test_data
0
 

Author Closing Comment

by:Chris Michalczuk
ID: 40248175
thanks
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
migrate a SQL 2008 to 2016, 2 34
Webservices in T-SQL 3 35
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 42
Why I am having this error in my query? 2 26
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question