Post reply

Varchar to money\decimal

  • August 5, 2016 at 4:04 pm

    #320486

    Converting varchar to money\decimal,

    Is the below correct?

    Below gives me 7000 but I was needing 70.00

    declare @deposit varchar(10)

    set @deposit =+000000007000

    ---+000000007000

    select (COALESCE(CAST(NULLIF(ISNUMERIC(@deposit), 1) AS MONEY), @deposit))

    Thanks.

  • August 6, 2016 at 1:49 pm

    #1894198

    People are probably scratching their heads at that code. Maybe you could explain what it's trying to do.

    The first thing is that a varchar(10) is declared but a numeric value is assigned to it immediately. That doesn't really make sense.

    isnumeric returns 1 "when the input expression evaluates to a valid numeric data type; otherwise it returns 0". In this case, it will return 1. Then NULLIF will set the return value to NULL.

    I'll leave it at that for now.

  • August 6, 2016 at 1:58 pm

    #1894199

    I have column with +000000007000 value and data type of column in varchar, I am trying to convert that data column to money\decimal.

    Thanks.

  • August 6, 2016 at 3:17 pm

    #1894202

    Well, I could guess that much but when you assign a value to @deposit, you don't give it a varchar , you give it a numeric. So sort that first.

  • August 7, 2016 at 2:52 pm

    #1894249

    There are no decimal places in the value: SQL can't possibly know to "adjust" for 2 decimal places from a varchar value. Therefore, if you need such an adjustment, make it yourself.

    declare @deposit varchar(10)

    set @deposit =+000000007000

    select (COALESCE(CAST(NULLIF(ISNUMERIC(@deposit), 1) AS money),

    CASE WHEN @deposit LIKE '%.%' THEN @deposit ELSE CAST(@deposit AS money) / 100.00 END))

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • August 7, 2016 at 3:55 pm

    #1894251

    Thank you for your reply.

    declare @deposit varchar(10)

    set @deposit =+000000007000

    select (COALESCE(CAST(NULLIF(ISNUMERIC(@deposit), 1) AS money),

    CASE WHEN @deposit LIKE '%.%' THEN @deposit ELSE CAST(@deposit AS money) / 100.00 END))

    This would give output :

    70.0000000000

    In order for me to get upto two decimal only I would use this?

    declare @deposit varchar(10)

    set @deposit =+000000007000

    select (COALESCE(CAST(NULLIF(ISNUMERIC(@deposit), 1) AS money),

    CASE WHEN @deposit LIKE '%.%' THEN @deposit ELSE CAST(@deposit AS money) / 100 END))

  • August 7, 2016 at 3:56 pm

    #1894252

    The value for deposit comes in form of text file with + in front of the numeric value that is the reason I had it as varchar.

  • August 8, 2016 at 8:59 am

    #1894365

    Since you don't have quotes around the assigned value, the +000000007000 is first being converted to an int value, and then that value is converted to varchar(10). Naturally your actual running code may not have this same issue. Besides, the value is too long for a varchar(10) if you did put quotes around it.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • August 8, 2016 at 9:27 am

    #1894380

    I assume the varchar(10) is just for the sake of the example.

    If you're reading these as strings from a text file, I think you want to convert to numeric as shown and then divide by 100.00 to add 2 decimals to the end.

    Be aware this really only works for currencies that are limited to 2 decimals. If this code were used in another currency that might not allow decimals, or allow more, this would cause issues.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 8, 2016 at 9:53 am

    #1894393

    You've rewritten a CASE statement in about as obscure a way as possible.

    -- Your version

    ISNULL(CAST(NULLIF(ISNUMERIC(@deposit), 1) AS money), @deposit)

    -- The straightforward version

    CASE WHEN ISNUMERIC(@deposit) = 1 THEN CAST(@deposit as money) ELSE 0 END

    To see that this is the case, lets follow the logic.

  • ISNUMERIC will only ever output 0 or 1.
  • The NULLIF will pass the 0 and change the 1 to a NULL
  • The cast will convert the 0 to $0 and the NULL from a null integer to a null money
  • Finally, when the value is NULL, the ISNULL will implicitly cast @deposit to money

    • So if the input is numeric, the output will be CAST(@deposit AS money), but if the output is not numeric, the output will be 0.

    An alternative approach, which is still clear, but not as wordy as the CASE statement would be to write

    ISNULL(TRY_CAST(@deposit AS money), 0)

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply