July 30, 2008 at 11:16 am
I am having some difficulty modifying/altering some data with a basic update SQL command and was looking for some help. I am trying to update a new data column based on data in another column. For example I have a column (price) with data 32.15 and would like to store the value in a new column (price2) as 00000032150 where the last three numeric places are the decimal places out to thousandths. Additionally if the data is -32.15 I would like to store the value as 00000032150-. Any assistance with this would be greatly appreciated.
Thank you,
John
July 30, 2008 at 1:17 pm
Here is something that works although it can be hard to read. You could also do it in steps. My example uses the AdventureWorks database:
[font="Courier New"]SELECT
UnitPrice,
/* Step 1 (innermost to outermost) - Convert the numeric field to have a precision of 3
Step 2 convert to varchar
Step 3 Replace the decimal with nothing ''
Step 4 get the length of the string
Step 5 subtract the length from 11 (This is how many digits you had in your example
Step 6 Replicate the character '0' 11-string length times to get the string to 11 digits
Step 7 concatenate the replicated 0's to the converted string
Step 8 when the value is negative append a '-'*/
REPLICATE('0', 11 - LEN(REPLACE(CONVERT(VARCHAR(38), CONVERT(DECIMAL(38,3), UnitPrice)), '.', ''))) +
REPLACE(CONVERT(VARCHAR(38), CONVERT(DECIMAL(38,3), UnitPrice)), '.', '') +
CASE
WHEN UnitPrice > 0 THEN ''
ELSE '-'
END AS stringUnitPrice
FROM
Sales.SalesORderDetail[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2008 at 11:31 pm
Jack - Thx your code does what I am looking for however I cannot use it in an update statement
update Table
set Price2 = REPLICATE('0', 11 - LEN(REPLACE(CONVERT(VARCHAR(38), CONVERT(DECIMAL(38,3), Price)), '.', ''))) +
REPLACE(CONVERT(VARCHAR(38), CONVERT(DECIMAL(38,3), Price)), '.', '') + CASE WHEN Price > 0 THEN '' ELSE '-'
Any idea on how I can get this to work in an update statement?
July 31, 2008 at 6:56 am
The code should work in an update statement as well. I notice in the code you have posted you are missing the 'END' for the CASE statement and I don't know if that is the problem or just a cut and paste error. Anyway here is the same code doing an update in AdventureWorks (I added the UnitPrice2 column to the table):
[font="Courier New"]UPDATE sales.salesorderdetail
SET unitprice2 = REPLICATE('0', 11 - LEN(REPLACE(CONVERT(VARCHAR(38),
CONVERT(DECIMAL(38,3), UnitPrice)), '.', ''))) +
REPLACE(CONVERT(VARCHAR(38), CONVERT(DECIMAL(38,3),
UnitPrice)), '.', '') + CASE
WHEN UnitPrice > 0 THEN ''
ELSE '-'
END
WHERE
salesorderdetailid <= 20
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 1, 2008 at 12:29 am
Jack - Thanks for catching the missing END in the statement. It works fine for positive values however the negative values are returning different results. For example -10.65 is returning 00000-10650-. I also have a column with no decimals - for example quantity 30 that I would like to read 030 for positive and 030- for negative. Tried modifying the existing code to decimail = 0 with no luck.
Thank you for all of your help.
John
August 7, 2008 at 12:33 am
jaguil1023 (8/1/2008)
Jack - Thanks for catching the missing END in the statement. It works fine for positive values however the negative values are returning different results. For example -10.65 is returning 00000-10650-. I also have a column with no decimals - for example quantity 30 that I would like to read 030 for positive and 030- for negative. Tried modifying the existing code to decimail = 0 with no luck.Thank you for all of your help.
John
I actually got what I needed to work by adding ABS function to read as follows in case anyone is interested. Thanks for all your help.
UPDATE sales.salesorderdetail
SET unitprice2 = REPLICATE('0', 11 - LEN(REPLACE(CONVERT(VARCHAR(38),
CONVERT(DECIMAL(38,3), ABS(UnitPrice))), '.', ''))) +
REPLACE(CONVERT(VARCHAR(38), CONVERT(DECIMAL(38,3),
ABS(UnitPrice))), '.', '') + CASE
WHEN UnitPrice > 0 THEN ''
ELSE '-'
END
WHERE
salesorderdetailid <= 20
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply