Zero Padding a numeric value in Transact SQL
A quick one before I go home (and just because I used it 10 minutes ago)... I zero pad a numeric value like this:
DECLARE @mynumber INT, @padding INT SELECT @mynumber = 123, @padding = 8 SELECT REPLACE(STR(@mynumber, @padding), ' ', '0')
| 00000123 |
16 comments:
This just came in handy for me. I really hate formatting in T-SQL but every once in a while I have to.
Thanks!
Thanks so much-I was at the end of a busy day this was just what I was looking for.
Man, thanx... really worth XD
I use this method, which I find makes it pretty clear to other readers of my code that I'm trying to pad the number!
SELECT RIGHT('00000000'+ CAST(@mynumber AS VARCHAR(8)), 8)
Actually, I think it is the most elegant solution to this problem that I've seen.
Just what I was looking for. Great job!
This tried:
DECLARE @mynumber bigINT,
@padding INT
SELECT @mynumber = 219923741,
@padding = 10 - len(@mynumber)
SELECT REPLACE(STR(@mynumber, @padding), ' ', '0')
I am getting output as * :((
You are setting the total requested length to 1 (by doing @padding = 10 - len(@mynumber)) while you are trying to enter a number of 9 digits.
The example below works fine because here we are formatting the 9 digit number as a 10 digit string:
DECLARE @mynumber bigINT,
@padding INT
SELECT @mynumber = 219923741,
@padding = 10
SELECT REPLACE(STR(@mynumber, @padding), ' ', '0')
Thanks for this - it saved me a bit of time today.
What I tried
REPLACE(STR(I.OrganizationID,6),' ',0)
Thanks for your Idea.
Thanks for writing this.
event better is
DECLARE @mynumber numeric(5,2),
@padding INT
SELECT @mynumber = 123,
@padding = 8
SELECT REPLACE(STR(@mynumber, @padding,2), ' ', '0')
to accomodate the decimal number
"I use this method, which I find makes it pretty clear to other readers of my code that I'm trying to pad the number!
SELECT RIGHT('00000000'+ CAST(@mynumber AS VARCHAR(8)), 8)"
This works great!!! Keeps me from having to do separate queries using the len() command... this is one step and is awesome. Thanks whoever posted this.
SELECT RIGHT('00000000'+ CAST(@mynumber AS VARCHAR(8)), 8)
GREAT ONE. THANKS!
[quote]
SELECT RIGHT('00000000'+ CAST(@mynumber AS VARCHAR(8)), 8)
[/quote]
Works great, but not that elegant. Think about creating a key to match with another table, think about field lengthts of more than 10 positions...
Nice alternative is the Replicate-function:
SELECT RIGHT(REPLICATE('0',8)+ CAST(@mynumber AS VARCHAR(8)), 8)
--just check if length of number is less then pedding else don't do anything
DECLARE @mynumber INT,
@padding INT
SELECT @mynumber = 123,
@padding = 8
if LEN(@mynumber)<8
SELECT REPLACE(STR(@mynumber, @padding), ' ', '0')
Post a Comment