Why 0 is equal to empty string?

Question 1

I need some help in finding why the followingT-SQL statement returns1 (true):

SELECT IIF( 0 = '', 1, 0)

I guess someone has change anANSI option likeSET ANSI_NULLS or something else that is causing the behavior.

My issue is that I am joining some values and in the final row set I have values which are joined by0 and'' values, which is not correct.

Question 2

I don't know if this would help in your joining issue, but in general, you can use this to distinguish between0 and empty strings:SELECT LENGTH(0); -- returns 1. // SELECT LENGTH(''); -- returns 0;

Question 3

That is just documented behavior. I don't think anyone messed with the settings.

Seedata type precedence on MSDN.

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.

As noted in the comments the empty string gets converted to 0 in any numeric type and to 1900-01-01 00:00:00.000 when converted to a date.

EDIT: I think your real problem is that your design is so that you have to join on fields of a different data type. The only way to get around this is to have a convert on your join clause which will hurt query performance. The main problem is probably with the schema design

EDIT:There was a lot of discussion in the comments that have been moved to chat. However illogical it may seem, converting an empty string to other data types produces arbitrary values.

This code:

SELECT CONVERT(int, '')SELECT CONVERT(float, '')SELECT CONVERT(date, '')SELECT CONVERT(datetime, '')

Produces this output:

001900-01-011900-01-01 00:00:00.000

You could expect then that this behavior is consistent between other preceding datatypes and expect that converting 0 to a date would produce the same arbitrary value but it doesn't.

SELECT CONVERT(date, 0)

Produces

Explicit conversion from data type int to date is not allowed.

Because it's not asupported conversion

while

SELECT CONVERT(datetime, 0)

Returns

January, 01 1900 00:00:00

So yes, it's weird and arbitrary, but actually documented and explainable.

Question 4

Comments are not for extended discussion; the conversation about this answer has beenmoved to chat.

Question 5

Is the behaviorCAST('' AS INT) -> 0 documented somewhere? It would be nice if you add reference.

Question 6

@SalmanA: Itshould be documented in the "Converting Character Data" section of thechar/varchar documentation, but it currently isn't. I have left a feedback comment asking for it to be included.

Question 7

Are there any performance loss to mention if one just usesmyIntColumn ='' in sted of checking the datatype first and askingmyIntColumn =0 inside a where statement?

Tom V 15.8k7 gold badges66 silver badges88 bronze badges · Accepted Answer · 2016-02-14 16:16:26Z

That is just documented behavior. I don't think anyone messed with the settings.

Seedata type precedence on MSDN.

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.

As noted in the comments the empty string gets converted to 0 in any numeric type and to 1900-01-01 00:00:00.000 when converted to a date.

EDIT: I think your real problem is that your design is so that you have to join on fields of a different data type. The only way to get around this is to have a convert on your join clause which will hurt query performance. The main problem is probably with the schema design

EDIT:There was a lot of discussion in the comments that have been moved to chat. However illogical it may seem, converting an empty string to other data types produces arbitrary values.

This code:

SELECT CONVERT(int, '')SELECT CONVERT(float, '')SELECT CONVERT(date, '')SELECT CONVERT(datetime, '')

Produces this output:

001900-01-011900-01-01 00:00:00.000

You could expect then that this behavior is consistent between other preceding datatypes and expect that converting 0 to a date would produce the same arbitrary value but it doesn't.

SELECT CONVERT(date, 0)

Produces

Explicit conversion from data type int to date is not allowed.

Because it's not asupported conversion

while

SELECT CONVERT(datetime, 0)

Returns

January, 01 1900 00:00:00

So yes, it's weird and arbitrary, but actually documented and explainable.

Comments are not for extended discussion; the conversation about this answer has beenmoved to chat.
Is the behaviorCAST('' AS INT) -> 0 documented somewhere? It would be nice if you add reference.
@SalmanA: Itshould be documented in the "Converting Character Data" section of thechar/varchar documentation, but it currently isn't. I have left a feedback comment asking for it to be included.
Are there any performance loss to mention if one just usesmyIntColumn ='' in sted of checking the datatype first and askingmyIntColumn =0 inside a where statement?

Movatterモバイル変換

Stack Exchange Network

Why 0 is equal to empty string?

1 Answer1

Your Answer

Sign up orlog in

Post as a guest

Related

Hot Network Questions

Subscribe to RSS