Leading Zeros

Question

Post reply

Leading Zeros

jenkiwa

SSC Rookie

Points: 36
More actions
December 2, 2002 at 11:15 am

#101319

New to SQL Server but... Am I missing something or is it impossible to create any type of numeric column that will support leading zeros?

Viewing 13 posts - 1 through 12 (of 12 total)

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

Mkumari Hall of Fame Points: 3498 More actions · Answer 1

Mkumari

Hall of Fame

Points: 3498

December 2, 2002 at 11:53 am

#442820

No you cannot.

NPeeters SSChampion Points: 12554 More actions · Answer 2

Seems obvious, doesn't. You should only store 'useful' data in your table. Adding leading zeroes is a presentation function.

There should be some posts or articles around about that. I guess a combination of the STR, CONVERT and STUFF functions should do the trick.

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 3

numerics don't have leading 0s. you can use char if you want.

This is by convention. All numbers (in math) have an infinite number of zeros leading.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

jenkiwa SSC Rookie Points: 36 More actions · Answer 4

jenkiwa

SSC Rookie

Points: 36

December 2, 2002 at 2:40 pm

#442835

Thanks very much to all who responded!

Antares686 SSC Guru Points: 125444 More actions · Answer 5

If you need to present with a specifc length with leading 0's then I do as follow.

REPLICATE('0',overalllength-LEN(CAST(numericcol AS varchar(overalllength))) + CAST(numericcol AS varchar(overalllength))

Otherwise no leading 0's on numerics and it is better to store numerically instead of character type then add 0 unless length is variable.

David Burrows SSC Guru Points: 65143 More actions · Answer 6

When I want leading zeros I do

REPLACE(STR(numericcol,overalllength,numdecplaces),' ','0').

Antares686, which solution do you think is the more efficient or does it not matter?

Far away is close at hand in the images of elsewhere.
Anon.

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Opps foudn a missing ) in my item should read

REPLICATE('0',overalllength-LEN(CAST(numericcol AS varchar(overalllength)))) + CAST(numericcol AS varchar(overalllength))

didn't have a way to test when I wrote.

I would say about the same. CAST is becoming more of the norm to use but STR works just as well.

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 8

Here is another way provided you don't have to pad with to many zeroes.

http://www.geocities.com/sqlserverexamples/string4.htm

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

jenkiwa SSC Rookie Points: 36 More actions · Answer 9

This response is overwhelming! Again my thanks to all.

Wally

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 10

They all support them! They're just suppressed! hahahahahaha

jenkiwa SSC Rookie Points: 36 More actions · Answer 11

What does that mean?

quote:
They all support them! They're just suppressed! hahahahahaha

mdburr Default port Points: 1483 More actions · Answer 12

I think he means that at a binary level, your numeric field has a fixed set of bits assigned to it - an integer, for example, has 32 bits (4 bytes, where each byte is 8 bits long) - and so long as the value you've entered doesn't use all of the bits in that fixed set of bits, the nonused bits are all set to 0. Hence, you have several leading 0's in the binary data, but you can't see them (unless you use DBCC PAGE to look at the actual data page).