Solved

Zero filling zip code in SQL and converting from character to numeric

Posted on 2013-06-26
6
487 Views
Last Modified: 2013-06-26
Experts,

I am coverting a  column from character to numeric in SQL.  I would like to see the "0" at the leading zeroes.  Can someone tell me how to format the output?

This is what I have to convert from character to numeric.


,(select convert( numeric( 5, 0 ), left( member.member_zip, 5 ))) as zip
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
  • 4
  • 2
6 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39279130
>I am coverting a  column from character to numeric in SQL.  
>I would like to see the "0" at the leading zeroes.
What you are asking is not possible, as no numeric data types support leading zeros.
So ... it can't be done.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39279134
To 'pad' a number with leading zeros and display it..

Declare @num int = 1234

SELECT RIGHT('00000' + CAST(@num as varchar(max)), 5)

Open in new window

0
 

Author Comment

by:morinia
ID: 39279199
jihorn,

This gives me leading zeroes, but it is  1024 characters.  I want 5 positions numeric.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 66

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 39279231
>I want 5 positions numeric.
You can have this as 5 positions varchar, but as I stated above numeric is not possible.   No way, no how.

Declare @num int = 1234

SELECT CAST(RIGHT('00000' + CAST(@num as varchar(max)), 5) as varchar(5))

Open in new window

0
 

Author Closing Comment

by:morinia
ID: 39279269
Thanks
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39279277
Thanks for the grade.  Good luck with your project.  -Jim
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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

717 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