CODEWITHSUNDEEP
t-sqllocalization
okrumnow
okrumnow

Reputation: 2416

Localization for CONVERT

I need to join two tables based on columns with different types. The first column is a nvarchar(50) and the second column if of type float. The nvarchar-column contains a string representation of the float value, but written in german localization, which means, that the comma is used as decimal separator.

Example: The float 26.1 ist represented as 26,1 in the nvarchar column.

I tried JOIN ... ON firstcolumn = CONVERT(NVARCHAR(50), secondcolumn) which works for all numbers without fraction. Columns with fraction are translated into "26.1" which obviously doesn't match "26,1".

Using SET LANGUAGE German didn't work.

Is there a way to use german localization for the CONVERT statement, so that the comma instead of the dot is used? Or do i have to use REPLACE to solve the problem?

Upvotes: 1

Views: 361

Answers (1)

Oleg Dok
Oleg Dok

Reputation: 21776

You need to use REPLACE as 

firstcolumn = CAST(REPLACE(secondcolumn, ',', '.') as float)

BUT be very careful when comparing floating point types, sometimes better to use this construction:

ABS(firstcolumn - CAST(REPLACE(secondcolumn, ',', '.') as float)) < @epsilon

where @epsilon is acceptable error, say = 0.001

Upvotes: 2

Related Questions