CODEWITHSUNDEEP
sql-serverssms
JoshRoss
JoshRoss

Reputation: 335

Null at the beginning of a SQL string?

I was trying to compare two tables, and I kept getting values that looked empty, but where not.

 SET @secret_message = CHAR(0) + 'Hello World!';
 SELECT @secret_message, RIGHT(@secret_message,12)

From what I can tell, SSMS stops reading the values if they start with a NULL.

Is this a bug or feature?

Upvotes: 0

Views: 56

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415820

The bytes are there; it's the code to display the results that isn't showing them.

When SQL Server stores string data, it knows how many characters there are, regardless of the contents. Similarly, newer programming environments tend to encode strings by including the length as it's own field (usually the first field as fixed-length int)

However, older string handling libraries — especially those from C/C++, which in turn underpin much of our core operating systems and GUI desktop platforms — instead used \0-terminated strings. These libraries will walk the data until they find the \0 character and then stop.

So what we have here is SQL Server returning data that looks like this:

 \0Hello World!

Which is turn (properly!) rendered in an old-school string like this:

\0Hello World!\0

And therefore finally looks like this when we actually go to display it:



Upvotes: 6
Related Questions