March 27, 2018 at 4:16 am
Hi,
I get error 'Error converting data type varchar to float.' and I have a blind spot, I can't figure out why thus happens
In one CTE I replace commas with periods and then also return NULL if the resulting value does not valuate as numeric:
IIF(IsNumeric(REPLACE(Value, ',', '.')) = 0, NULL, Value) Value
and then in the next CTE I cast the value as float
CAST(Value AS FLOAT)
The first CTE returns a few million nice numbers and 60 000 NULL's as value - why is the CAST to float failing?
Select CAST(NULL as Float) Or Select CAST(' 123.123 ' as Float) works fine...
Ville
March 27, 2018 at 4:48 am
In the first CTE, you're testing whether the value can be converted to a numeric type (although ISNUMERIC isn't totally reliable, but that's another discussion). In the second, you're just doing the CAST, and therefore as soon as it hits a value that can't be converted to a number, it fails.
John
March 27, 2018 at 5:08 am
Hi,
yes, that is what happens..but what then? What can i do?
isNumeric not reliable..how can i the find what Value might cause this?
v
March 27, 2018 at 5:11 am
Have you tried TRY_CONVERT?
John
March 27, 2018 at 5:24 am
Hi,
no, but just did. Thank you, that opened my blind spot...
In this particular CSV file I returned Value instead of REPLACE(Value, ',', '.') from the first CSV and this file was not supposed to have any comma decimals but it did a few...
Ville
March 27, 2018 at 5:45 am
Hi again,
I found even better solution...?
TRY_CONVERT returns 0 from some of the decimals with comma but try_parse seems to be consistent...
TRY_PARSE(REPLACE(Value, ',', '.') AS Float)
V
March 27, 2018 at 8:07 am
WilburSmith - Tuesday, March 27, 2018 5:45 AMHi again,I found even better solution...?
TRY_CONVERT returns 0 from some of the decimals with comma but try_parse seems to be consistent...
TRY_PARSE(REPLACE(Value, ',', '.') AS Float)
V
Are you sure those commas are really decimal places and not thousand separators? And TRY_CONVERT would also work if you replace the commas with periods.
March 28, 2018 at 12:14 pm
Warning: pedantic Celko history lesson coming!
This actually has an interesting history to it. Europeans like using a comma for a decimal point. Americans (and therefore programming languages in the early days) like using a period for the decimal point. This got to be a debating point in the Algol 60 standard was being written; the Americans won. Syntax in Algol 60 (and just about every other programming language ever written) would be ambiguous if the comma was a decimal point
Fortran was the dominant programming language for scientific work at this point in our history. There were no IEEE standards at this time, so things pretty much defaulted to whatever IBM was using. But the internal representations for floating-point varied from vendor to vendor. In particular, we pretty much agree that using the letter E (we did not really have lower case letters on punch cards) as the symbol for base 10 (i.e. 510 2 = 500) Algol in some ways was better about this in their publication language; they had an explicit,subscript 10 instead is part of the language.. But the problem was we didn't get around to worrying about whether you should say"Exx","1Exx" or "0Exx" for floating-point constants.
When we got to SQL Standards, we had more floating-point options. We had a type REAL,FLOAT, and DOUBLE PRECISION in the first ANSI/ISO SQL Standards. REAL is not the same as FLOAT!
Today, most of the SQL vendors let you pick one of the many IEEE formats. I always recommend against using any of them and going for DECIMAL(S,P)datatypes. Many decades ago, I was a Fortran programmer (yes, I remember punchcards), and one of the things we had to do was take care of floating-point rounding errors by watching the way we wrote our code. I don't think people are taught how to do this anymore; it takes a fair bit of math, knowledge of the machine representation and experience.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply