Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

formatting character zip code to numeric zip code in SQL (SQL SERVER)

Posted on 2013-06-21
3
Medium Priority
?
465 Views
Last Modified: 2013-06-21
I have a zip code coming in as character 9?  Is there a way in the Select SQL statement to change the zip code to numeric 5?
0
Comment
Question by:morinia
[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
3 Comments
 
LVL 8

Accepted Solution

by:
didnthaveaname earned 2000 total points
ID: 39266914
You could use:

select convert( numeric( 5, 0 ), left( zipColumn, 5 ) );

Open in new window

0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39266920
can you provide examples what you are getting what you need?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39266938
>I have a zip code coming in as character 9?
Is this a question or a statement?  Mind readers we ain't.

>Is there a way in the Select SQL statement to change the zip code to numeric 5?
If you mean just insert the first five characters, and drop off the rest, then

SELECT LEFT(zip_code, 5)

If you mean something else, you'll need to give us details / sample data.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

721 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