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!)