Adding Leading Zeros to Integer datatype

Question

Adding Leading Zeros to Integer datatype

PJ_SQL

SSCertifiable

Points: 6703
More actions
September 9, 2016 at 10:56 am

#329955

When I insert data, it is cutting of leading zeros as the column I am inserting is of 'int' datatype.
Is there way to insert leading zeros to a integer datatype column?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No, because leading zeros are meaningless on numeric data. If you have something that should have leading zeros, it should be stored as a string, because it's not numeric data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Phil Parkin SSC Guru Points: 246961 More actions · Answer 2

Or you should leave it as INT and format your output such that it displays in the format you require.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Little known, little used, and you should not use this until you are displaying data or outputting it to a client program.

declare @int int = 123

select FORMAT(@int,'0#########')

I agree 100% that integers should be stored as integers and NOT as character strings.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 4

Actually, I think the FORMAT function has been shown to be unnecessarily expensive and should be avoided altogether.

FORMAT() is nice and all, but…

There have also been a few threads here on SSC where posters are getting equally poor results...

I'd just stick with the tried and true...

DECLARE @int INT = 123;

SELECT RIGHT(CONCAT('000000', CAST(@int AS VARCHAR(6))), 6)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Thanks Jason. I never format data in SQL itself, so I had no idea about the performance hit. But I just tested it for half a million rows against your code and it REALLY drags.

Forget my suggestion, go with Jason's. But ONLY when you are returning data to a calling program.

Once you make it a string you are complicating all further calculations and comparisons.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

The Dixie Flatline (9/9/2016)
Thanks Jason. I never format data in SQL itself, so I had no idea about the performance hit. But I just tested it for half a million rows against your code and it REALLY drags.
Forget my suggestion, go with Jason's. But ONLY when you are returning data to a calling program.
Once you make it a string you are complicating all further calculations and comparisons.

Just to confirm, the simple testing I did with FORMAT vs CONVERT showed that FORMAT was 44 times slower than CONVERT.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 7

FORMAT or CONVERT or STR - they all return varchar data type.

On an attempt to save their outcome into int column it will be converted back to int and all the zeros will be dropped.

Formatting is relevant only to string presentations of integers in reports, interfaces, etc.

_____________
Code for TallyGenerator