January 20, 2004 at 10:27 am
In my query I'm suppose to have a lot of new items, and I was wondering how I can receive the following:
--
-Lars
Please only reply to this newsgroup. All mails would be bounced back.
January 20, 2004 at 11:08 am
I use a little udf like
(@Num varchar(20),
@Length int)
Returns VarChar(20)
-- Returns passed integer value left-padded with zeros for length of passed @Length
-- ie passed value of 123,6 becomes "000123"
-- Print dbo.fn_LPadZero(-123, 6)
Begin
declare @Result varchar(20)
if left(ltrim(@Num),1) = '-' begin
set @Result = '-' + right('00000000000000000000' + replace(convert(varchar(20), @Num), '-', ''), @Length-1)
end else set @Result = right('00000000000000000000' + convert(varchar(20), @Num), @Length)
return @Result
End
Once you understand the BITs, all the pieces come together
January 20, 2004 at 11:12 am
Thanks - but here is a similar function; and easier to understand
-Lars
Please only reply to this newsgroup. All mails would be bounced back.
January 20, 2004 at 11:23 am
Thanks Lars
Yes, can be simplified... just remember to deal with negative values if appropriate.
Once you understand the BITs, all the pieces come together
January 21, 2004 at 7:05 am
For non negative values I prefer
REPLACE(STR(512,15,0),' ','0')
Far away is close at hand in the images of elsewhere.
Anon.
January 21, 2004 at 7:52 am
Lars, try the various methods. depending on your data, the REPLACE() may run 5-10 time slower than the REPLICATE() or RIGHT(). Also consider if -1 = '-00001' or '0000-1' for negatives or other non-positive integer data you might pass the function.
Once you understand the BITs, all the pieces come together
August 30, 2005 at 5:14 pm
Works like a charm. Thank you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply