June 6, 2003 at 9:48 am
Is there a way to keep leading zeroes in a table without converting a decimal/integer value into a string?
I would like to have 009 in a numeric field but it save it as 7.
Thanks
June 6, 2003 at 11:18 am
quote:
I would like to have 009 in a numeric field but it save it as 7.
I think you meant save it as 9, but in any case, this is a formatting issue. Keep the field numeric in the table, and on SELECTs, manipulate the number with string functions:
-- Example
declare @1 numeric
set @1 = 9
select RIGHT('0000000000' + CONVERT(VARCHAR(10), @1), 10)
Sorry if this isn't particularly elegant, but it will store the number in the most efficient way...
HTH,
Jay
June 6, 2003 at 12:14 pm
Thanks,
Yes, I meant 9. I will use your technique.
--Peter
June 9, 2003 at 1:24 am
A technique I have used regardless of programming language when wanting to display a numeric with leading zeroes, goes a bit like this:
right( str ( 10 ^ 7 + fld ) , 7 ).
However this only works with positive integers. The number of zeroes should be match the second argument to the right function.
Example
declare @zeroes int
set @zeroes = 7 /* number of digits to be displayed */
declare @userval bigint
set @userval = 458 /* lets take a value */
print right ( str ( power(10,@zeroes ) + @userval ) , @zeroes )
/*
This allows you to change number of digits to display.
However if you know the number of digits you want,
just replace with the specific values
*/
print right ( str ( 10000000 + @userval ) , 7 )
Robert
June 9, 2003 at 10:23 am
Robert,
That method works great if you already know what the size of the value is. If you do not know (if the value could be any number of digits, but still want a particular number of leading zeroes, you would want to run some len checks. e.g. len(@uservalue).
Just a suggestion.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply