VARCHAR to DECIMAL string manipulation
I need some help with an incredibly ugly DECIMAL string manipulation. The datatype for all values used in these calculations is varchar. (I know. There's nothing I can do about it.) I have two different groups of calculations -- I need one to always come back with 3 decimals, and I need the other to always come back with 2 decimals. By itself, that is not too bad... I thought I could use something like this -- CONVERT(varchar(20), CAST(SUM(value) as MONEY),1) --- where CONVERT( varchar, m, 1 ) gives me the value into a string with commas, but it's just not coming together for me.
Again, the two groups of calculations below -- I need the top one to always return 3 decimals with ROUND, and I need the bottom one to always return 2 decimals --- whether the values are whole or not.
Is anyone able to help?
Always display 3 decimal points, whether it is a whole number or not -- ie., 200.000, .015 -- ROUNDing up for anything 4 and greater. .0539 becomes .054
1. CAST(ROUND((mmmm / oooo), 2) AS FLOAT)
2. CAST(LWength AS VARCHAR(20)) + ' oz" x 1" piece'
3. CAST(WPLength AS VARCHAR(20)) + ' oz/' + MinWidth + '" x 1" piece'
For these, I just need to always display 2 decimals, whether the value is a whole number or not -- ie., 1.00, .50
1. MinWidth + ' x ' + CAST(LPWWeight AS VARCHAR(20)) + '"/oz'
2. CAST(LPWeight AS VARCHAR(20))+ ' sq in/dwt'
3. MinWidth + ' x ' + CAST(LPWeight AS VARCHAR(20)) + '"/oz'
4. CASE
WHEN LPWeight >= 2 THEN
CAST(LPWWeight AS VARCHAR(20)) + ' ft/oz'
ELSE
CAST(LPWeight * 12 AS VARCHAR(20)) + ' in/oz' END
Again, the two groups of calculations below -- I need the top one to always return 3 decimals with ROUND, and I need the bottom one to always return 2 decimals --- whether the values are whole or not.
Is anyone able to help?
Always display 3 decimal points, whether it is a whole number or not -- ie., 200.000, .015 -- ROUNDing up for anything 4 and greater. .0539 becomes .054
1. CAST(ROUND((mmmm / oooo), 2) AS FLOAT)
2. CAST(LWength AS VARCHAR(20)) + ' oz" x 1" piece'
3. CAST(WPLength AS VARCHAR(20)) + ' oz/' + MinWidth + '" x 1" piece'
For these, I just need to always display 2 decimals, whether the value is a whole number or not -- ie., 1.00, .50
1. MinWidth + ' x ' + CAST(LPWWeight AS VARCHAR(20)) + '"/oz'
2. CAST(LPWeight AS VARCHAR(20))+ ' sq in/dwt'
3. MinWidth + ' x ' + CAST(LPWeight AS VARCHAR(20)) + '"/oz'
4. CASE
WHEN LPWeight >= 2 THEN
CAST(LPWWeight AS VARCHAR(20)) + ' ft/oz'
ELSE
CAST(LPWeight * 12 AS VARCHAR(20)) + ' in/oz' END
PortletPaul
Could you provide some (relevant/representative) sample data for each requirement please?
ASKER
Sorry. I should have included that. Let me elaborate a little.
This temporary table is loaded first, and used as a working table. The two columns - WPL, LPW - are DECIMAL(5,0) in the table they are pulled from. and all values are numeric -- 1, 5, 3200, 6,10, etc., up to as many as 5 digits.
SELECT DISTINCT
MATNR,
MEINH,
CAST(ROUND((UMREZ / UMREN), 2) AS FLOAT) [WPL],
CAST(ROUND((UMREN / UMREZ), 2) AS FLOAT) [LPW],
CAST(CHARINDEX('sheet', Product.ShortDescription) AS BIT) IsSheet,
CASE ISNUMERIC(AUSP1)
WHEN 1 THEN
CAST(CAST(AUSP1 AS FLOAT) AS VARCHAR(20))
ELSE
''
END
INTO #temptable
That data is selected INTO a table first (above), and then loaded into this 2nd temp table for additional manipulation. As you can see here, they go into the 2nd table as VARCHAR(256). Again, I know this is not ideal at all, but I cannot get them to change their approach. I've already tried.
CREATE TABLE #temptable2
(
MATNR VARCHAR(18) NULL,
FormattedItemNumber VARCHAR(10) NULL,
WPL VARCHAR(256) NULL,
LPW VARCHAR(256) NULL
);
It is the final pull from this table that I need to return with the 2 / 3 decimals, within the two groups of statements that I posted in the first part of the question.
So. To answer your question, all data is numeric values, but not numeric datatype in the final return that I need to do with 2 and 3 decimals.
This temporary table is loaded first, and used as a working table. The two columns - WPL, LPW - are DECIMAL(5,0) in the table they are pulled from. and all values are numeric -- 1, 5, 3200, 6,10, etc., up to as many as 5 digits.
SELECT DISTINCT
MATNR,
MEINH,
CAST(ROUND((UMREZ / UMREN), 2) AS FLOAT) [WPL],
CAST(ROUND((UMREN / UMREZ), 2) AS FLOAT) [LPW],
CAST(CHARINDEX('sheet', Product.ShortDescription) AS BIT) IsSheet,
CASE ISNUMERIC(AUSP1)
WHEN 1 THEN
CAST(CAST(AUSP1 AS FLOAT) AS VARCHAR(20))
ELSE
''
END
INTO #temptable
That data is selected INTO a table first (above), and then loaded into this 2nd temp table for additional manipulation. As you can see here, they go into the 2nd table as VARCHAR(256). Again, I know this is not ideal at all, but I cannot get them to change their approach. I've already tried.
CREATE TABLE #temptable2
(
MATNR VARCHAR(18) NULL,
FormattedItemNumber VARCHAR(10) NULL,
WPL VARCHAR(256) NULL,
LPW VARCHAR(256) NULL
);
It is the final pull from this table that I need to return with the 2 / 3 decimals, within the two groups of statements that I posted in the first part of the question.
So. To answer your question, all data is numeric values, but not numeric datatype in the final return that I need to do with 2 and 3 decimals.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for the late response. Why is ROUND not necessary?
you get the same effect by using format()
try it
click this: http://rextester.com/OPYKU95563
note that rextester uses Germanic number conventions, but you will see that by using format(...,'N2') or 'N3' you get the same effect as using round
This link http://sqlfiddle.com/#!18/e0ee8a/1 should also allow you to try it for yourself, but it was slow when I tried it just now, so that why I used rextester instead.
You can continue to use round() if you prefer, it does "document" the code, and I initially used it simply because I am so used to using round() too.
try it
click this: http://rextester.com/OPYKU95563
note that rextester uses Germanic number conventions, but you will see that by using format(...,'N2') or 'N3' you get the same effect as using round
This link http://sqlfiddle.com/#!18/e0ee8a/1 should also allow you to try it for yourself, but it was slow when I tried it just now, so that why I used rextester instead.
You can continue to use round() if you prefer, it does "document" the code, and I initially used it simply because I am so used to using round() too.
ASKER
FORMAT works perfectly, PortletPaul. Thank you. I still would like to understand why ROUND is not needed.
ASKER
I would like to omit the commas, though. In the resulting value. Any way other than REPLACE ?
ASKER
Yes, I ran them side by side, too, but I wanted to know if there was another reason. It looks good, portlet. Thank you very much!
>>"Any way other than REPLACE ?"
I don't think so.
Glad you got your solution. Cheers.
Paul
I don't think so.
Glad you got your solution. Cheers.
Paul