Convert nvarchar to float or number in sql

I recently came across the following usefull SQL query, Maybe you’ll find it useful.
In SQL, convert nvarchar (string) to number can be achieve by using cast and convert SQL function.
For example, I have Following table

You may notice that UnitPrice has nvarchar datatype.
Here is my Query to convert UnitPrice datatype to float

SELECT [Name]
      ,[Description]
      ,[Unit]
      ,convert(float,[UnitPrice]) [UnitPrice]
      ,[CreateDate]
  FROM [Product]

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

Reason: Have a close look at the table, iMac UnitPrice is 1,200.00. UnitPrice 1,200.00 have coma which stop sql to convert into float.
Final Query:

SELECT [Name]
      ,[Description]
      ,[Unit]
      ,convert(float,replace([UnitPrice],',','') )[UnitPrice]
      ,[CreateDate]
  FROM [Product]

Thanks

One response to “Convert nvarchar to float or number in sql”

Leave a Reply