data conversion problem

cipriano555
cipriano555 used Ask the Experts™
on
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
) ON [PRIMARY]

it contains this value:


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


testval
--------------------------------------------------
$1,000,000.00

(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?

Thanks,

C
Comment
Watch Question

Do more with

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

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

Author

Commented:
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

Commented:
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