data conversion problem

cipriano555 used Ask the Experts™
I'm trying to convert an nvarchar that holds a formatted numerical value into a FLOAT.

For example, we have  a table...

CREATE TABLE [dbo].[aadata_test](
      [testval] [nvarchar](50) NULL

it contains this value:

SELECT [testval]
  FROM [IntegratedMedicalBillingDataWarehouse].[dbo].[aadata_test]


(1 row(s) affected)

I want to convert it to a number, so I try this:

SELECT cast([testval] as float)
  FROM [IntegratedMedicalBillingDataWarehouse].[dbo].[aadata_test]

But get this error message.

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.

How do you do this kind of conversion to get a numerical value?


Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
SELECT CAST(REPLACE(REPLACE(testval, '$', ''), ',', '') AS float)
FROM [IntegratedMedicalBillingDataWarehouse].[dbo].[aadata_test]
Top Expert 2010

BTW, CAST has no trouble converting your original value to data type money.  If you must have a float, you can then convert the money to float:

CAST(CAST([testvalu] AS money) AS float)


Thank you for your help,  matthewspatrick.  

I do not understand the point of the money data type, why wouldn't you just use float?  Maybe this kind of conversion issue is the point?
Top Expert 2010

The money data type is useful for monetary transactions: it is capable of storing large numbers, and it is guaranteed to be precise to 4 decimal places.

Float is OK if you do not care too much about precision.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial