The curious case of CHAR(0)

10

November 14, 2019 by Kenneth Fisher

Over the years I’ve seen quite a few strange things with SQL Server. This one may not have been the oddest, but it was definitely up there. A user asked me to help them copy a hashed password from one server to another. (Note: As this was a password I was careful to make sure to get this approved by management before even looking at it.) This was an application password and was stored in a normal varchar(100) column. Nothing exciting about the collation either. (SQL_Latin1_General_CP1_CI_AS). But when I looked at the data this is what I saw:

SELECT MyPass FROM #Password;

Now that’s odd right? The MyPass column in the first row looks empty, and in fact if I copy and paste it I don’t get anything. (The second row was added for control so you can see nothing there is nothing special about the table.) But look at this:

SELECT MyPass, len(MyPass) AS ColLen, 
     datalength(MyPass) AS ColDataLength 
FROM #Password;

So I have data! Trim functions didn’t affect the results at all. So what’s going on? My next step was this:

SELECT MyPass
	,ASCII(SUBSTRING(MyPass,1,1)) AS Char1
	,ASCII(SUBSTRING(MyPass,2,1)) AS Char2
	,ASCII(SUBSTRING(MyPass,3,1)) AS Char3
	,ASCII(SUBSTRING(MyPass,4,1)) AS Char4
FROM #Password;

Ok, now things are getting interesting. An ASCII value of 0? I’ve never heard of that. I honestly didn’t know it was possible. As it happens, yes, it’s a real value and in SSMS it does a few strange things.

You see how it makes the value disappear in the grid output right? In the text view it shows up but as a regular space.

SELECT MyPass FROM #Password;

The way I decided it was a regular space is by copying it and pasting it into a varchar variable and then checking the ASCII value of that first character again. This time I got back a 32 (a space).

On the other hand if you print the value to the message window it acts like the grid view and you only get values before the character?!? Try copying the output of this and paste it somewhere?!?

PRINT '|' + CHAR(0)+'abc' + '|';

In the end to copy the data over I used the ASCII values, put them into the CHAR function (one at a time) and created a string out of it. It was a bit hokey but it worked.

If you want to follow along with the above tests here is the code I used to create the temp table:

CREATE TABLE #Password (MyPass varchar(100));
INSERT INTO #Password VALUES (CHAR(0) + 'ABC'), ('ABC');

Note: None of this is stuff you should be using (at least deliberately) in production unless you are someone like Rob Volk (blog|twitter) who likes to use SQL tricks to make people cry (If you ever get to see his Revenge The SQL presentation do so!)

10 thoughts on “The curious case of CHAR(0)

  1. denisgobo says:

    Just so you know… this

    select ‘|’ + CHAR(0)+’abc’ + ‘|’;

    in gridview displays

  2. denisgobo says:

    I believe that char(0) is a null teminator… it is a way to terminate a string in c.. (IIRC) so SSMS might think the string is terminated and stops display after it encounters it

    another nice one is char(160) a nbsp; (non breaking space).. however you can’t trim it and len will also count it compared to a regular space (char (32) )

    Denis

  3. djk says:

    Another remarkable thing is that you can’t even handle the char(0) with most sql functions. Try replacing it with REPLACE(MyPass, CHAR(0), ‘_‘). Doesn’t work.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,085 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: