Varchar to money\decimal

Question

Varchar to money\decimal

PJ_SQL

SSCertifiable

Points: 6703
More actions
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.
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

DouglasH SSCommitted Points: 1669 More actions · Answer 1

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.

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 2

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.

DouglasH SSCommitted Points: 1669 More actions · Answer 3

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.

ScottPletcher SSC Guru Points: 101119 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 5

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

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 6

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.

ScottPletcher SSC Guru Points: 101119 More actions · Answer 7

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 8

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.

drew.allen SSC Guru Points: 76996 More actions · Answer 9

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.