Tag Archives: convert string to number

Convert string with $ and , 000’s separator to a number

You have imported some data from a file or Sharepoint list and the numbers have come across as strings, in their display format.  Unfortunately for you this means if you want to perform some arithmetic or sorting on this column, you will need to convert to a number and remove the formatting characters like $ , -.

If you do try to perform some arithmetic on this column directly, you will most likely get the error … “Error converting data type varchar….”.

Now there is nothing like a clean datamart to store this data, however the following example demonstrates how to perform this function in an adhoc query.   The code sample searches the varchar column aptly named “Varchar Actual ($)” and replaces any symbols containing the characters $ , – and the space character with empty string…allowing the string to be successfully cast to a number…


([Varchar Actual ($)])),'$',''),',',''),'-','') as float)

AS [Float - Actual ($)]

If some of your values contain valid negative you will of course need to preserve the leading “-” character.

Reference …

Social MSDN Thread

Cast and Convert

Cast and Convert Basics

Some REPLACE basics with Bru ! Have a Cuppa with Bru