Solved

strip out characters and build zip codes cleanly in sql

Posted on 2014-07-24
6
197 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

626 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