SQL server doesn't implicitly cast from VARCHAR to FLOAT in some scenarios

Question

SQL server doesn't implicitly cast from VARCHAR to FLOAT in some scenarios

Nandu S Raj 105

Update: I have pasted the actual image here instead of the link.

--This works fine
DECLARE @sampleVar FLOAT = '123.034';
SELECT @sampleVar ;

--This throws error
SELECT '123.034'*-1;

When I try to do the above, I notice that the implicit CAST is happening for variable assigment , but I get a cast exception for the last query.

As per the below image which I snipped from MS docs, it says implicit cast is supported.

User's image

Is this inconsistency expected? Also , in the image it says an explicit cast is required to preserve precision - are we referring to CAST function in specific or is it irrespective?

2 answers

Your answer

Answer 1

Dan Guzman 9,411

--This throws error

SELECT '123.034'*-1;

The constant -1 is an integer literal so SQL Server attempts to convert the varchar '123.034' value to integer per data type precedence rules. This results in an error due the fractional value.

Specify float constant to avoid the error:

SELECT '123.034'*-1E0;

Nandu S Raj 105 Reputation points

2024-11-26T16:26:44.5766667+00:00

If you look closely in the image , it says conversion implicit conversion is possible from varchar to integer as well.
Dan Guzman 9,411 Reputation points

2024-11-26T16:33:48.44+00:00

@Nandu S Raj , the implicit conversion of varchar to integer is possible but not with a varchar constant that contains a fractional decimal value. You will not get an error with a value that doesn't have a fractional value (e.g. SELECT '123034'*-1;).
Nandu S Raj 105 Reputation points

2024-11-26T17:02:12.9533333+00:00

@Dan Guzman

So the presence of integer '-1' forces sql server to try and convert the varchar constant to integer, but it fails to do so why? is where I am confused again, my understanding is that SQL server will truncate whatever comes after the period and convert it into an integer in which case , shouldn't it be 123*-1 which equals -123.

I really appreciate you taking time out to reply btw. Thanks!
Dan Guzman 9,411 Reputation points

2024-11-26T17:32:57.16+00:00

@Nandu S Raj , SQL Server does not try to truncate the varchar value during conversion to integer. The attempt results in conversion error to data type int due to the presence of the decimal.
Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2024-11-26T22:31:49.2633333+00:00
This fails:

SELECT convert(int, '123.800')

Dots are not legal in integer literals.

This works:

SELECT convert(int, convert(decimal(8,3), '123.800'))

You have a decimal value which you convert to int.

Yes, one can argue that this is inconsistent. Then again, the interpretation of a string is not the same as the interpretation of a number.
Nandu S Raj 105 Reputation points

2024-11-29T11:36:26.6066667+00:00

@Erland Sommarskog Thanks for the reply! Would like to know more as to why the inconsistency if there though.

if '123.3'->123.3 is possible and 123.3 to 123 is also possible I was confused why '123.3' to 123 is not possible.

Also , in the diagram , are they trying to say that an implicit cast is possible to int ONLY if the string looks like an integer ? varchar x int is marked as implicitly convertible.
Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2024-11-29T22:23:42.5733333+00:00

In the end, these are just rules that were set up once upon a time. Once set up, they are not likely to change, because of backwards compatibility.

You can argue both ways. There certainly are language where '123.3' will be 123 when you say ToInt or whatever the syntax might be. But you can also argue that the dot is an alien character in an integer value.

The diagram only shows if an implicit conversion may be possible at all. For those where there is a red cross, you cannot convert at all. if there is a blue square, you can convert, but you need to convert explicitly by using the CAST or CONVERT functions. They are not needed where the orange circles appear. But conversion will still fail, it the input format is not considered legal.

Answer 2

Hi @Nandu S Raj

So the presence of integer '-1' forces sql server to try and convert the varchar constant to integer, but it fails to do so why?

The reason is a varchar value could contain characters not just numeric values. It is hard to build a logic to handle the convert behavior.

As we know, not only a varchar like '123.800' to an int but also these conversations: varchar 'A123' or '123.8,123.6' to int will be failed. SQL Server doesn't know how to handle the non-numeric characters or decimals in a varchar string.

However, when converting between numeric types like float or decimal to int, the logic is simple, SQL Server will just truncate the decimal part because it knows it is safe to remove the decimal part.

Therefore, when trying to convert a varchar to int, SQL Server expects the varchar to contain only solid int values.

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Nandu S Raj 105 Reputation points

2024-11-29T11:43:49.1133333+00:00

Thanks for the answer Cosmog!If we go by that reason shouldn't the conversion to float also be equally challenging?

Share via

SQL server doesn't implicitly cast from VARCHAR to FLOAT in some scenarios

2 answers

Your answer