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

DECLARE @NumberInStr NVARCHAR(10)

SET @NumberInStr = '92,45'

-- this two will fail
SELECT CAST(@NumberInStr AS FLOAT)
SELECT CONVERT(FLOAT, @NumberInStr)
GO

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:

DECLARE @NumberInStr NVARCHAR(10)

SET @NumberInStr = '92,45'

SET @NumberInStr = REPLACE(@NumberInStr, ',', '.')

SELECT CAST(@NumberInStr AS FLOAT)
SELECT CONVERT(FLOAT, @NumberInStr)
GO

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:

DECLARE @NumberInStr NVARCHAR(10)

SET @NumberInStr = '92,45'

SELECT TRY_PARSE(@NumberInStr AS FLOAT USING 'Cs-CZ')

SELECT TRY_PARSE(@NumberInStr AS FLOAT USING 'en-US')
GO

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:

DECLARE @NumberInStr NVARCHAR(10)

SET @NumberInStr = '92,45'

SELECT CAST(@NumberInStr AS MONEY)
SELECT CONVERT(MONEY, @NumberInStr)

SELECT CAST(@NumberInStr AS MONEY) / 100
SELECT CONVERT(MONEY, @NumberInStr) / 100
GO

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 thousands separator. Let’s test the same with a comma and combine both separators together:

DECLARE @NumberInStr NVARCHAR(10)

SET @NumberInStr = '92.45'

SELECT CAST(@NumberInStr AS MONEY)
SELECT CONVERT(MONEY, @NumberInStr)

SET @NumberInStr = '10,023.76'

SELECT CAST(@NumberInStr AS MONEY)
SELECT CONVERT(MONEY, @NumberInStr)
GO

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 your ideas in the comments.