November 22, 2011 at 5:49 pm
Hi,
I was given the requirement to display a decimal data column as string for a particular column. The string format is 20 characters long and 5 being allocated for decimal places. The sign for the value would need to be in the front as well.
e.g. -3.45 would need to be displayed as -00000000000000345000
I have numerous ways to do this but cannot output the required format. Anyone got any ideas?
RIGHT(REPLICATE('0',20) + STR(CAST([Quantity] AS DECIMAL),20,5),20) AS [QuantityDisplay]
November 22, 2011 at 10:15 pm
Is this what you require?
DECLARE @Quantity AS DECIMAL(20,5)
SET @Quantity = -3.45
IF @Quantity < 0
BEGIN
SET @Quantity = @Quantity * -1
SELECT '-' + RIGHT(REPLICATE('0',20) + CAST(@Quantity AS VARCHAR(20)),20) AS [QuantityDisplay]
END
ELSE
SELECT '-' + RIGHT(REPLICATE('0',20) + CAST(@Quantity AS VARCHAR(20)),20) AS [QuantityDisplay]
November 22, 2011 at 10:19 pm
Multiply QUANTITY in Ron's answer by 100000 first, convert it to an INT, and then apply Ron's answer.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2011 at 10:27 pm
Jeff Moden (11/22/2011)
Multiply QUANTITY in Ron's answer by 100000 first, convert it to an INT, and then apply Ron's answer.
Thanks Jeff - I goofed reading the OPs question and I thank you for the correction of my error
November 23, 2011 at 5:03 pm
No problem, Ron. Your solution had just about everything in it. Nicely done.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2011 at 3:48 pm
Thanks Ron and Jeff. Great solution 🙂
I tweaked it a bit to remove the '.' character as well as ensure the sign is counted towards the total length of the string.
DECLARE @Length int
DECLARE @Decimal int
SET @Length = 20
SET @Decimal = 5
DECLARE @Value AS DECIMAL(@Length,@Decimal)
SET @Value = 3.45
IF @Value < 0
BEGIN
SET @Value = @Value * -1
SELECT '-' + RIGHT(REPLICATE('0',@Length - 1) + REPLACE(CAST(@Value AS VARCHAR(250)),'.',''),@Length - 1) AS [valDisplay]
END
ELSE
SELECT RIGHT(REPLICATE('0',@Length) + REPLACE(CAST(@Value AS VARCHAR(250)),'.',''),@Length) AS [valDisplay]
I am planning to put this into a function at a later stage and calling it from within my T-SQL. I need the function to be able to pass in the length of the return value as well as the decimal places of the number.
It seems that I cannot declare the decimal variable with dynamic length and decimal places though. Any idea how to do this?
DECLARE @Value AS DECIMAL(@Length,@Decimal)
November 27, 2011 at 9:52 pm
Bruce Li
It seems that I cannot declare the decimal variable with dynamic length and decimal places though. Any idea how to do this?
Jeff Moden (11/22/2011)
--------------------------------------------------------------------------------
Multiply QUANTITY in Ron's answer by 100000 first, convert it to an INT, and then apply Ron's answer.
Right now my only thought would be to apply Jeff Moden's recommendation before passing the value into the function.
November 28, 2011 at 7:20 am
You and Jeff have answerred my original question Ron. The T-SQL works fine if I make the decimal declaration static length and decimal places.
I wasn't sure if I should start another topic for this or not. I was wondering if it is possible to declare a variable with dynamic lengths using another variable.
November 28, 2011 at 9:50 am
If you want to make the length and decimal places dynamic, you could do this:
DECLARE @Length int
DECLARE @Decimal int
SET @Length = 20
SET @Decimal = 5
DECLARE @Value AS FLOAT
DECLARE @IntValue AS BIGINT
SET @Value = -3.54
SET @IntValue = CAST(@Value * CAST('1' + REPLACE(SPACE(@Decimal),' ','0') AS INT) AS BIGINT)
IF @IntValue < 0
BEGIN
SET @IntValue = @IntValue * -1
SELECT '-' + RIGHT(REPLICATE('0',@Length - 1) + REPLACE(CAST(@IntValue AS VARCHAR(250)),'.',''),@Length - 1) AS [valDisplay]
END
ELSE
SELECT RIGHT(REPLICATE('0',@Length) + REPLACE(CAST(@IntValue AS VARCHAR(250)),'.',''),@Length) AS [valDisplay]
November 28, 2011 at 10:08 am
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply