conversion between nvarchar float and language impact

Question

when i do

select cast('2,2' as float)
replace('2,2' , ',' , '.' ) is needed before casting

i have the following error " Error converting data type varchar to float." because there is the comma.

when i do

 select cast('2.2' as float)

the result is "2,2" with a comma

i am a bit lost with that since i integrate excel files with french formatted numbers: commas "," instead of "." so i have tons of theses errors

thankss!

SSMS is displaying the result according to the regional settings on your machine, but the SQL Server you are running against has different (likely default, American) regional settings. — Aaron Bertrand, Jan 30 '19 at 13:55
@Ven I think the OP wants to either always see `2.2` or always see `2,2`. Probably the latter. — Aaron Bertrand, Jan 30 '19 at 13:57
@AaronBertrand Aaron: Do u think he wants to see it in output or input ? — Ven, Jan 30 '19 at 13:58
Possible duplicate of [SQL Server format decimal places with commas](https://stackoverflow.com/questions/13245663/sql-server-format-decimal-places-with-commas) — DDS, Jan 30 '19 at 13:59
https://dba.stackexchange.com/questions/118057/convert-string-numeric-values-with-comma-as-decimal-separator-to-numeric10-2 — Sebastian Brosch, Jan 30 '19 at 14:09
@Ven Your guess is as good as mine. At the root I'm sure they just want to understand why it's different. — Aaron Bertrand, Jan 30 '19 at 14:14
@AaronBertrand I guess excel import into SQL server is causing 2,2 instead of 2.2 , he will simply need to replace or cast it at the import layer — Ven, Jan 30 '19 at 14:17

score 0 · Answer 1 · answered Jan 30 '19 at 14:01

SQL Server Documentation states that the notation needed to be used for casting numeric/float characters to numeric format is Scientific Notation

Numbers are displayed with 6,720,000,000 => 6.72×109 in Scientific Notation

Thus the answer is : Regardless of Regional Settings input format is always fixed to Scientific Notation

DDS · Answer 2 · 2019-01-30T14:08:48.550

0

this should work

declare @rate double
set @rate = 1.954
select     FORMAT(@Rate, 'G', 'fr-fr') as formatted_decimal

1,9540

will print

edited Jan 30 '19 at 14:08

answered Jan 30 '19 at 14:01

DDS

score -1 · Answer 3 · answered Jan 30 '19 at 14:09

-1

please use this i think this will help you

select cast(replace('2,2',',','.') as float)

answered Jan 30 '19 at 14:09

Mohammad Shehroz

3 Answers3