Unexpected query results

问题:

Why I get following results from sql-server?

SELECT '' + 12 C1, CONVERT(int, '') C2,
       CASE WHEN '' = ' ' THEN 'equal' ELSE 'not equal' END C3

Sql-Server Fiddle demo

--Results
| C1 | C2 |    C3 |
-------------------
| 12 |  0 | equal |

EDIT: C3 has been answered. Everyone answered thinking '' + 12 = 12 is string concatenation but it is a mathematical operation. It is not clear why (NOT how) '' is converting to a 0 in sql-server.

回答1:

SQL Server has a strange feature whereby, if two strings are compared, and they are not of equal length, then it will pad the shorter one to the length of the longer one and then compare them. The padding character is a space. http://support.microsoft.com/kb/316626

C1 & C2 are just implicit & explicit conversion of an empty string to 0.

回答2:

This is - more or less - expected behaviour. From a copy of the SQL (ISO/ANSI) Standard:

The comparison of two character string expressions depends on the collation used for the comparison. When values of unequal length are compared, if the collation for the comparison has the NO PAD characteristic and the shorter value is equal to some prefix of the longer value, then the shorter value is considered less than the longer value. If the collation for the comparison has the PAD SPACE characteristic, for the purposes of the comparison, the shorter value is effectively extended to the length of the longer by concatenation of <space>s on the right.

Now, most DBMS have implemented string comparisons with slight differences. In SQL_Server and MySQL, you'll find that '', ' ', '  ' and '   ' (strings with 0, 1, 2 and 3 spaces) are all equal, no matter if they are defined as VARCHAR or CHAR.

In Postges, they are all unequal if they are VARCHAR but equal if CHAR (so no padding in VARCHAR columns). If one of them is VARCHAR and one CHAR, then they are found equal, so I guess that padding is done before comparison.

Oracle is similar to Postgres with the extra peculiarity that the empty string '' behaves (almost everywhere) as NULL. So, when you compare it to the strings with one or more spaces (or to itself), the result is neither True nor False but UNKNOWN. Oracle has one more difference, if one string is defined as VARCHAR and the other as CHAR, the comparison is rather complicated. From tests, I assume that only CHAR are padded in that case, up to their (defined datatype) length and then compared to the unpadded VARCHAR.

You can check (all 4 DBMS) in the SQL-Fiddle

回答3:

C1 - An implicit conversion is happening here, the empty string gets converted to 0. This explains why you are getting 12 (`0 + 12).

C2 - Explicitly converting '' to an integer results in 0. A bit artibrary, but that's how it works (and might change - as far as I know this is not documented, if anyone knows otherwise, please correct me).

C3 - See the answer by muhmud - essentially when comparing strings of different lengths, the shorter one first gets padded with spaces to the length of the longer.