February 4, 2014 at 3:40 pm
Run the following:
Declare @v-2 nVarChar(4000);
-- Test 1
Set @v-2='Select * from sys.indexes where type=0';
Select @v-2,Len(@v),DataLength(@v),Cast(@v as VarBinary(128));
Print @v-2;
RaisError(@v,10,1) with NoWait;
Exec(@v);
Exec sp_executesql @v-2;
-- Test 2
Set @v-2='Select * from sys.indexes'+Char(0)+'where type=0';
Select @v-2,Len(@v),DataLength(@v),Cast(@v as VarBinary(128));
Print @v-2;
RaisError(@v,10,1) with NoWait;
Exec(@v);
Exec sp_executesql @v-2;
With Test 1 everything does what you'd expect, however, with Test 2 the Char(0) in the string causes some strangeness. :crazy:
Select and RaisError only display the string up to the Char(0), but Print displays the entire string, even though both strings show the same Len() and DataLength(). Exec and sp_ExecuteSQL also see the entire string as exemplified by their respective outputs. As indicated in this post's title this strangeness occurs with both VarChar and nVarChar data types. Internally this most likely is exposing an inconsistent use of the CRT and/or null-terminated strings.
Excuse me while I use this "feature" to tighten up a couple of co-workers... 🙂
(sorry, got a little carried away with the emoticons...)
February 5, 2014 at 9:19 am
I would not be surprised at all to see inconsistent and/or buggy handling of NULL "character" in a string. I would file a Connect item if you feel it worthy.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 5, 2014 at 9:30 am
Over the years I've noticed this and other anomalies regarding (n)VarChar.
Another is that (n)VarChar(max) is the black-sheep of the SQL data-types in that intrinsic functions will work with it but some return unexpected results; you need to use (n)VarChar(4000/8000) to get the expected results.
Just knowing about these anomalies and coding around them is way quicker than going through Microsoft to get them resolved (ya, its a cop out, but who has the time?).
February 5, 2014 at 1:27 pm
Peter E. Kierstead (2/5/2014)
Over the years I've noticed this and other anomalies regarding (n)VarChar.Another is that (n)VarChar(max) is the black-sheep of the SQL data-types in that intrinsic functions will work with it but some return unexpected results; you need to use (n)VarChar(4000/8000) to get the expected results.
Just knowing about these anomalies and coding around them is way quicker than going through Microsoft to get them resolved (ya, its a cop out, but who has the time?).
It's not an "anomoly". As Kevin implied, CHAR(0) is the NULL character. To be honest, I'm surprised that anything at all returned to the screen.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 6, 2014 at 2:53 am
Jeff Moden (2/5/2014)
It's not an "anomoly". As Kevin implied, CHAR(0) is the NULL character. To be honest, I'm surprised that anything at all returned to the screen.
It's only in C code that a null character terminates a string--there's no reason it should do so in SQL server, any more than any other non-printing character should. (Or should CHAR(26) also end a string, since that's the DOS termination character? :-)).
February 6, 2014 at 5:14 am
Select and RaisError only display the string up to the Char(0), but Print displays the entire string, even though both strings show the same Len() and DataLength(). ...
First of all, SELECT returns full strings in both cases. The difference you see, is most likely related to the mode you are displaying results - try switching into "text" mode and difference dissapear (similar effect for showing results in grid when selecting string values containing new line characters).
PRINT vs RAISERROR, here is a clue: PRINT displays whatever passed into it without any additional work, while RAISERROR parsing the message looking for substitution parameters, most likely using printf from C standard library (check BoL - there is a reference to it), so char (0) looks like causes this logic to fail, try to report bug to MS 🙂 ....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply