# 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
LVL 18
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

freelancerCommented:
Could you provide some (relevant/representative) sample data for each requirement please?
0
Author Commented:
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.
0
freelancerCommented:
I need one to always come back with 3 decimals, and
I need the other to always come back with 2 decimals

``````|          wpl2 |          lpw2 |           wpl3 |           lpw3 |          wpl |            lpw |
|---------------|---------------|----------------|----------------|--------------|----------------|
|      2,345.77 |        786.88 |      2,345.765 |        786.877 | 2345.7654321 | 786.8765432987 |
|     17,896.00 |          6.88 |     17,896.000 |          6.877 |        17896 |       6.876543 |
| 23,457,654.30 | 87,654,987.00 | 23,457,654.300 | 87,654,987.000 |   23457654.3 |       87654987 |
``````
This approach assumes you can use the FORMAT() function available with & since SQL Server 2012

``````select
ca1.*, wpl, lpw
from #temptable2
cross apply (
select
format(round(cast(WPL as decimal(30,4)),3),'N2') wpl2
, format(round(cast(LPW as decimal(30,4)),3),'N2') lpw2
, format(round(cast(WPL as decimal(30,4)),3),'N3') wpl3
, format(round(cast(LPW as decimal(30,4)),3),'N3') lpw3
) ca1
``````
``````CREATE TABLE #temptable2
([MATNR] varchar(18),
[FormattedItemNumber] varchar(10),
[WPL] varchar(256), [LPW] varchar(256))
;

INSERT INTO #temptable2
([MATNR], [FormattedItemNumber], [WPL], [LPW])
VALUES
('matnr', 'ItemNumber', '2345.7654321', '786.8765432987'),
('matnr', 'ItemNumber', '17896', '6.876543'),
('matnr', 'ItemNumber', '23457654.3', '87654987')
;
``````
Once you have converted/rounded the numbers, format() returns a string, so you can add the extra twiddly bits without needing further conversion, just use the column aliases for those strings as given in the cross apply. e.g.

select ca1.wpl2 + ' oz" x 1" piece'  as xyz

also see: http://sqlfiddle.com/#!18/e0ee8a/1

ps, you don't need to use round()...
``````select
ca1.*, wpl, lpw
from temptable2
cross apply (
select
format(cast(LPW as decimal(30,4)),'n2') wpl2
, format(cast(LPW as decimal(30,4)),'n2') lpw2
, format(cast(LPW as decimal(30,4)),'n3') wpl3
, format(cast(LPW as decimal(30,4)),'n3') lpw3
) ca1
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Sorry for the late response.  Why is ROUND not necessary?
0
freelancerCommented:
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.
0
Author Commented:
FORMAT works perfectly, PortletPaul.  Thank you.  I still would like to understand why ROUND is not needed.
0
Author Commented:
I would like to omit the commas, though.  In the resulting value.  Any way other than REPLACE ?
0
Author Commented:
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!
0
freelancerCommented:
>>"Any way other than REPLACE ?"

I don't think so.