Timeline for Why are zero length spaces being returned from a Union All against an empty table in SQL Server 2005 but not SQL Server 2012
Current License: CC BY-SA 3.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 2, 2014 at 20:27 | history | migrated | from stackoverflow.com (revisions) | ||
| Feb 4, 2014 at 22:09 | comment | added | usr |
Note, that LEN ignores trailing spaces. LEN(' ') = 0. That's normal. According to msdn.microsoft.com/en-us/library/ms190309.aspx the char(2) should be converted to varchar. Maybe a 2005 bug? Unlikely.
|
|
| Feb 4, 2014 at 21:24 | comment | added | GunnerFan420 | @NathanSkerl I'm not sure but it seems to have something to do with the char(2) column in the empty table used for the 2nd part of the Union. If I change Unit6 in the 2nd query to an empty string '' then I don't see the issue. | |
| Feb 4, 2014 at 20:59 | comment | added | nathan_jr |
The len makes sense, in that the length of an empty char(2) is still 0, select len(cast('' as char(2))), datalength will tell the truth, but what is causing the char(2) cast in the subquery?? I am unable to reproduce
|
|
| Feb 4, 2014 at 19:33 | comment | added | GunnerFan420 | I've added the @@Version information for both servers to the tail end of my original post. Should have included them initially as you did. | |
| Feb 4, 2014 at 19:16 | comment | added | GunnerFan420 | You've nailed it on the weirdness. I was first trying to track this down by checking field lengths. How in the heck you can have a zero length field which shows two spaces when you concatenate is beyond me. | |
| Feb 4, 2014 at 19:15 | comment | added | GunnerFan420 | Seems to me that the issue is performing a UNION of two records where a field in one contains an empty string ('') and the same field in the other is a selected char(x) field from a database with no records. Still not sure why it would work differently between the two SQL versions. | |
| Feb 4, 2014 at 19:03 | comment | added | BlackICE | what's really weird is that even your query results say len(unit6) is 0, but something is still inside the spanner | |
| Feb 4, 2014 at 19:01 | comment | added | BlackICE | idk either, I haven't found a setting yet that gives me the results you're seeing. | |
| Feb 4, 2014 at 19:00 | comment | added | GunnerFan420 | I'm not sure how should always be but I'd expect it would be consistent between server versions. No idea why you didn't see the same results. | |
| Feb 4, 2014 at 18:47 | comment | added | BlackICE | Actually it doesn't matter when the column is NOT NULL anyway, it should always be padded to the length of the column. | |
| Feb 4, 2014 at 18:41 | comment | added | GunnerFan420 | I'm using two exact copies of the database so the ANSI Padding Status for all columns in all tables are identical. | |
| Feb 4, 2014 at 18:35 | history | answered | BlackICE | CC BY-SA 3.0 |