Converting string values with thousands separator to numeric value

It’s the common task to import CSV files into a staging area in the database where columns are using some character data type like VARCHAR and conversion to proper numeric type is done later. It can be a little bit confusing if our input data contains numbers stored with different decimal separators then is currently compatible with our SQL Instance settings. This can happen easily when saving data from Excel to CSV files where decimal separator depends on local computer national settings. Let’s observe it on a simple example.

We will try to convert the string variable value 92,45 with comma as a decimal separator to FLOAT numerical data type:

Execution has failed because of the comma as a decimal separator used.

The easiest option how to fix this error is to simply replace the comma with a dot before conversion:

Don’t scratch your head about the comma in the results set. That’s because it was formatted by SSMS to my national Windows settings.

TRY_PARSE() function is another cool option on how to solve this problem:

There are two points to consider:

  • You need to know the proper culture to be used.
  • You should somehow handle the NULL value output otherwise your data will be silently lost in the background. Or you can use the PARSE() function which will strictly fail on parsing error instead of NULL to be returned.

The last option we have is the MONEY datatype we can play with:

This time we should be more careful because MONEY datatype understands both separators: dot and comma same time. Dot is handled as decimal places separator and comma as a thousand separator. Let’s test the same with a comma and combined both separators together:

That’s in short how to handle the problem. I’m sure there is a lot of other clever ways how to solve the separator issue. You can keep you ideas in Comments.

Leave a Reply

Your email address will not be published. Required fields are marked *