Formating Numbers 1

[Andel]

Is there a way in SQL where you can format a number the way you want it like those other programming languages? Like for example, number 123, I like it to become 0000123. Andel
maingel@hotmail.com

 

[Cagliostro]

You can convert it to char and add '0000'. John Fill

 

[foxdev]

This is off the top o' my head, but try something like:

[tt]select REPLICATE('0', 8 - DataLength(MyColumn)) + MyColumn as MyNewColumn[/tt]

This should pad out to 8 characters. Robert Bradley

http://www.foxdev.com/

Coming Soon:

http://www.lowcostprints.com

http://www.agrainofhope.org

 

[Andel]

Thank you guys for your response. I tried to REPLICATE the '0' but it doesn't work for numbers, only varchars.

Sample data:

123 should become 00000123.00
2222 should become 00002222.00
12345 should become 00012345.00
99.9 should become 00000099.90
888.88 should become 00000888.88

Any other ideas? Thank you!



Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[jballum]

This will give you a string 8 characters long with zeroes padded on the front of a number:

RIGHT('0000000' + CONVERT(varchar(8), @int), 8)

JB

 

[Andel]

Hi JB, it work! Your code seems so simple but I don't understand how it works. Can you explain?

Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[jballum]

Andel,

1. Produces a string up to eight characters long out of the value in @int. ie. CONVERT(varchar(8), @int)

2. Concatenate '0000000' seven zeroes to the produced string. ie. '0000000' + CONVERT(varchar(8), @int)

3. Return the 8 rightmost characters of the string with the concatenated zeroes. ie. RIGHT('0000000' + CONVERT(varchar(8), @int), 8)

JB

 

[Andel]

Aha! Thank you.

Andel
andelbarroga@hotmail.com

http://andyb.8m.com