Geeks With Blogs
Nagendra Prasad
Formatting numbers to add leading zeros can be done in SQL Server. It is just simple. Lets create a new table and see how it works:

CREATE TABLE Numbers(Num INT);

Table Created.

Lets insert few values and see:

    INSERT Numbers VALUES('12');
    INSERT Numbers VALUES('112');
    INSERT Numbers VALUES('12');
    INSERT Numbers VALUES('122');
    INSERT Numbers VALUES('122');

1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.

Now we can see how the numbers are formatted with 6 digits, if it has less than 6 digits it will add leading zeros.
Data:

SELECT * FROM Numbers;

Num
12
112
12
122
122

5 row(s) affected.

Formatting:
SELECT RIGHT('00000'+ CONVERT(VARCHAR,Num),6) AS NUM FROM Numbers;

NUM
000012
000112
000012
000122
000122

5 row(s) affected. Posted on Thursday, March 19, 2009 12:15 PM | Back to top


Comments on this post: Formatting number to add leading zeros - SQL Server

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Sweet ! Thanks for the tip.
Left by Craig on Jul 10, 2009 10:33 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
thanks a lot, good one
Left by dinal on Jul 27, 2009 12:32 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Very, very clever!
Left by Nancy on Oct 06, 2009 12:52 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Thanks for the tip
Left by Gregorio Reyes on Oct 08, 2009 2:39 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Cool, and for negative numbers we have:
SELECT RIGHT('00000'+ CONVERT(VARCHAR,-2),6);

0000-2

Really COOL!
Left by yarick on Nov 20, 2009 10:52 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Awesome little solution. Simple and effective.
Left by Sandy on Dec 03, 2009 4:34 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
thank you
Left by Nam Nguyen Thanh on Dec 30, 2009 11:11 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
It saved ton of my time. Thanks
Left by kk on Jan 11, 2010 5:14 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
thanks for the Code.

Very Good .
-Arun
Shrinivas Technologies
Left by Arun B M on Feb 15, 2010 10:07 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
wonderful tip
Left by gautham on Mar 04, 2010 11:26 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
this is sooo sweet tips...........
Left by Shohel on Mar 23, 2010 8:16 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Your article is really very interesting. This is the information, I’ve been looking for… Thanks
BUY AMBIEN
Left by Kate on Apr 26, 2010 12:24 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Thanks alot.
Left by Giri on May 04, 2010 7:04 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Little.. but great !! Very helpful after searching lots of sites for solutions... thanks
Left by JigneSH on May 09, 2010 12:06 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Awesome application of mind...

Gr8...

Thanks
Left by Braj on Jun 01, 2010 8:23 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Thanks !

Helpful !
Left by Narinder Jit Singh on Jun 25, 2010 4:59 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Wow, Genius, Intelligent, Thanks A lot!
Left by waasim on Jul 12, 2010 12:05 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
great solution, thx
Left by W0333y on Jul 19, 2010 7:46 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
thank you so much
Left by lakshmipathy on Jul 26, 2010 6:30 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Thx for the instrucruion. Look if I've done right or not here СДЕЛАТЬ САЙТ ПЕТРОЗАВОДСК
Left by lurry on Oct 23, 2010 7:54 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Thanks mate, is really very clever!!
Left by Marcio on Nov 04, 2010 5:36 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Thanks a bunch - saved me time figuring it out :)
Left by Deb on Nov 24, 2010 1:37 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Thanks,,
Nice . i search lot of website . but i get the solution here only. thaks alot
Left by Ravindran on Dec 10, 2010 2:43 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Check out the value of NUM with more than 6 digit

-- Its dangerous :-)
Left by Kiran on Dec 14, 2010 5:47 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
wow, that is useful info!
Buy generic paxil Online
buy cheap Zyrtec
Left by Meyidentity on Dec 14, 2010 7:23 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Thanks for this, but I have another question regarding this :

My database fields looks like :
1.00000000 (a 1 with 8 decimals) which I want to convert and export to:
000001.000

So 10 character with always 3 decimals.

Can somebody help me with that?
Left by Joost van der Meer on Jan 18, 2011 12:28 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
this is very help full for me
Thanks a lots
Left by Rajdeep on Feb 10, 2011 5:25 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
This is an awesome tip! Thank you so very much for sharing it!
Left by Jaes W Overley on Feb 24, 2011 11:30 AM

# Good one
Requesting Gravatar...
Smooth. Big help. Thanks!

FYI I'm working in ColdFusion and had issues w/a query returning anything with leading zeros into an AJAX binded object; cfselect in my case. It passes the query as JSON which automatically drops leading zeros. I forced a linefeed character, #Chr(10)#, in the select to make it work. Dirty, but effective. :)

Happy coding!
Left by John M on Feb 25, 2011 12:59 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
good, you helped me.... thanks.
Left by vvv on Mar 03, 2011 3:57 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
really thank dear.
Left by ganpat on Mar 23, 2011 8:24 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
You are my hero. I wish I'd thought of that!
Left by Michael on Mar 31, 2011 1:51 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Can someting similar be done with a char field?
Left by Nate on Apr 01, 2011 12:29 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Great solution!
Left by Esteban on Apr 05, 2011 9:04 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
My problem is slightly different - I join two systems in personid - one system uses leading zeros , the other doesn't. So persin with ID 0001 in system A is matched with person with ID 1 in system B leading to a cartenian product. Any suggestions?
Left by Alison on Apr 08, 2011 4:48 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Great tip, thanks for posting.
Left by Azonic on Jun 10, 2011 2:18 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Simple but bailed me out today - thanks!
Left by hwallbanger on Jul 12, 2011 1:13 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
This doesnt work, as the results would be :
0000012
00000112
0000012
00000122
00000122

your just concatenating the string '00000' with the number ¬¬
Left by Eder on Jul 14, 2011 11:23 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Thanks man. But how do I do it in access ??
Left by Santosh on Aug 01, 2011 3:39 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Crap, only works with positive numbers
Left by Fred Flintstone on Aug 11, 2011 9:57 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Perfect, exactly what I was looking for.
Thanks.
Left by jim on Aug 30, 2011 4:00 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Thank you very much
Left by ALEX on Oct 02, 2011 5:49 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Thank you very much for your help, cheers :)
Left by ALEX on Oct 02, 2011 5:49 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
That worked perfectly.
Left by Mike Klaarhamer on Oct 04, 2011 2:04 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
hah, very clever. thanks!
Left by chuck on Oct 13, 2011 5:55 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Muy bueno, gracias.
Left by Dani on Nov 05, 2011 3:32 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Hi,

This is a great idea to solve this situation.

Thanks,
Chen
Left by Chen Noam on Nov 09, 2011 8:02 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Thank you so much for this.
Left by Lidia on Dec 13, 2011 3:15 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Awesome, thanks a lot.
Left by Aga on Jan 03, 2012 6:19 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
thanks for the code
Left by Accountants on Jan 07, 2012 12:53 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
great! works good
Left by Accountants on Jan 07, 2012 12:54 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
thanks again
Left by Accountants on Jan 07, 2012 12:55 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Thanks for sharing
Left by Chandralekha on Jan 29, 2012 7:28 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Thanks a lot. Nice trick.
Left by Don Smith on Feb 08, 2012 5:10 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
too tricky
Left by VIJAY on Feb 10, 2012 7:59 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Nice solution
Left by Vijay on Apr 16, 2012 8:49 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Very nice solution
Left by Kanagasabai on Apr 19, 2012 7:09 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Thanks!! it was simple , smart solution thanks
Left by Anthony on Apr 30, 2012 10:47 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
very nice solution. it's very simple to format. Thx.
Left by Somnath on Jul 12, 2012 10:01 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
thanks a looot :)
simple and its what i was looking for
Left by rakesh on Aug 28, 2012 6:31 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
finally i found the solution. thks
Left by suhartanto on Sep 07, 2012 7:50 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Good work.
Left by Supermang on Dec 03, 2012 9:59 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
So simply beautiful it brings tears to the eyes.

Many thanks!
Left by yomandan on Feb 28, 2013 3:17 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Nice! Short and sweet!
Left by Vivek Todi on Mar 03, 2013 7:33 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Perfect. Works for my Scenario.. Thanks
Left by Anil on May 14, 2013 3:45 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Excellent code
Left by Soumen Ghosh on May 22, 2013 12:02 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
But how can i set it permanently using update? Please help thx
Left by herbert on Aug 15, 2013 4:35 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
@herbert:
Something like this?
[code]
update tbl
set column1 = right('000000'+cast(column1 as varchar),6)
[/code]
Left by Bert on Sep 03, 2013 5:33 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
The method I usually employ for this is as follows:

SELECT REPLACE(STR(Num, 6), ' ', '0') AS NUM
FROM Numbers
Left by Celtic Gizmo on Sep 25, 2013 5:26 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
Very helpful. Thanks so much!
Left by Eric H on Oct 01, 2013 2:51 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
thinks for the tip
Left by ahmed on Oct 20, 2013 7:31 PM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
To cope with negatives too, you could use something like this..

DECLARE @Nums TABLE (Num INT)

-- Populate Table Variable with data
INSERT @Nums
SELECT 1223
UNION SELECT 712
UNION SELECT 12
UNION SELECT 6
UNION SELECT 0
UNION SELECT -6
UNION SELECT -12
UNION SELECT -712
UNION SELECT -1223

-- Define width of number
DECLARE @Digits Integer
SET @Digits = 5

SELECT CASE WHEN Num < 0 THEN '-' ELSE ' ' END +
RIGHT(Replicate('0',@Digits) + CONVERT(VARCHAR,Abs(Num)), @Digits)
FROM @Nums
Left by Adrian Parker on Dec 10, 2013 12:01 AM

# re: Formatting number to add leading zeros - SQL Server
Requesting Gravatar...
How to add zero to the left to the float number like '.3'.
Left by Suman Ganguly on Feb 26, 2014 6:43 PM

Your comment:
 (will show your gravatar)
 


Copyright © nagendraprasad | Powered by: GeeksWithBlogs.net | Join free