Solved

strip out characters and build zip codes cleanly in sql

Posted on 2014-07-24
6
187 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 142

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 142

Expert Comment

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

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

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 …
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 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