Solved

strip out characters and build zip codes cleanly in sql

Posted on 2014-07-24
6
195 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 49

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

734 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