Insert zero before a numeric datatype in SQL Server

Question

I was trying insert a value in a column in SQL Server which is of type numeric(18, 0).

I am unable to insert a zero at the beginning. For example adding 022223 gets inserted as 22223...

I think changing the column type to varchar will work but I don't want to alter table structure.

Any way to do this without changing the table structure..Please help

You could also add the zero when selecting from the database, which should lead to the same effect. — Eggi
– Eggi, Commented Jun 9, 2012 at 8:28
A numeric value is numeric - it doesn't have leading zeroes other formatting applied to it. If you need to show it with leading zeroes - do so in your reporting tool or your UI frontend - that's not the job of SQL Server. — marc_s
– marc_s, Commented Jun 9, 2012 at 8:28
@Eggi all values dont have leading zeros.hence that could not be done — Karthik
– Karthik, Commented Jun 9, 2012 at 8:29

Andrius Naruševičius · Accepted Answer · 2012-06-09 08:30:46Z

1

There is no point to have this IN a database. You will, afterall, select the data, won't you? So while selecting do something like this. I looked in google for "mssql numeeric leading zeros". All of the solutions are like in the link I have mentioned :) Or obviously, use varchar if you, for some reason, must have data like that in a table :)

answered Jun 9, 2012 at 8:30

Andrius Naruševičius

8,6289 gold badges53 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Karthik Over a year ago

actually all variables dont have a zero before them so i cant append values before them.May be i should change the database column time to varchar

marc_s Over a year ago

@karthi: do NOT change the datatype just for display purposes! If the value you're storing is indeed a number, you should use a numeric datatype! The data being stored shouldn't be altered just for some display "cosmetic" - you can always change the display on reports or the UI of your application. Do NOT pick a "wrong" datatype just to handle some display requirement! You'll regret it - rather sooner than later.....

Behnam Esmaili · Accepted Answer · 2012-06-09 09:23:23Z

1

you can apply a transformation when reading and inserting the value like this:

when inserting value :

string s = "00056";
 double val = double.Parse("0." + s);

and when querying value use:

double value = 0.00056; // stored value in your db field
 s = val.ToString().Remove(0,2);

i think it will work in any case - whether you have leading zero in your value or not.

answered Jun 9, 2012 at 9:23

Behnam Esmaili

5,9776 gold badges36 silver badges64 bronze badges

Comments

Samy Vilar · Accepted Answer · 2012-06-09 17:48:07Z

0

Well I dont think it's possible, since its being stored as a 32 bit so simply add leading zeros when printing it ... There are multiple approaches. http://sqlusa.com/bestpractices2005/padleadingzeros/

edited Jun 9, 2012 at 17:48

answered Jun 9, 2012 at 8:31

Samy Vilar

11.2k2 gold badges42 silver badges34 bronze badges

Collectives™ on Stack Overflow

Insert zero before a numeric datatype in SQL Server

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related