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…


CAST(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM

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

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s