CODEWITHSUNDEEP
sql-serversql-server-2008t-sqltype-conversion
M4N
M4N

Reputation: 96576

How to safely convert NVARCHAR to MONEY

I have an existing table with a 'price_info' column of type NVARCHAR. I'd like to add a new column 'price' of type MONEY and (where possible) convert/copy the contents of the 'price_info' column into it.

If 'price_info' contains only numeric data, then the value can easily be converted. But of course 'price_info' can also contain data which cannot be converted to type MONEY in which case an error occurs.

Is there a way to copy only the values where the conversion to MONEY is successful, and silently ignore all other rows (or set the 'price' column to 0 in these cases)?

E.g. what I'd like to have is something like this (pseudo-code):

update products set price = some_conversion_function(money, price_info, 0)

(where 0 is the default value to be used if the conversion fails).

Upvotes: 3

Views: 4731

Answers (1)

StevenMcD
StevenMcD

Reputation: 17482

If you need to do this via script only then you can run 2 updates.

Update 1: Run an update and set the price where ISNUMERIC is true

update products 
set price = convert(money, price_info)
where ISNUMERIC(price_info) = 1

Update 2: Run an update to set the price to 0 where ISNUMERIC is false.

update products 
set price = 0
where ISNUMERIC(price_info) = 0

You can get the documentation on ISNUMERIC here http://msdn.microsoft.com/en-us/library/ms186272.aspx

Upvotes: 3

Related Questions