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
dbaSQLAsked:
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.

PortletPaulfreelancerCommented:
Could you provide some (relevant/representative) sample data for each requirement please?
0
dbaSQLAuthor 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
PortletPaulfreelancerCommented:
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 |

Open in new window

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

Open in new window

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')
;

Open in new window

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

[edit]
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

I don't think so.

Glad you got your solution. Cheers.
Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.