Casting CONTEXT_INFO to varchar and the resulting length

Question

I'm trying to use CONTEXT_INFO to pass a usercode from a stored procedure into a DELETE trigger for table auditing purposes.

It all works fine, however I noticed that the length of the usercode saved in the audit table was not correct.

Take this script as an example...

declare @userCode varchar(50)
set @userCode = 'TestUser'

declare @binary_userCode varbinary(128)
set @binary_userCode = cast(@userCode as varbinary(128))
set CONTEXT_INFO @binary_userCode

declare @temp_userCode varchar(50)
set @temp_userCode = (select cast(CONTEXT_INFO() as varchar(50)))

--set @temp_userCode = rtrim(ltrim(@temp_userCode))

select @userCode, len(@userCode), @temp_userCode, len(@temp_userCode)

set CONTEXT_INFO 0x

Results:

len(@userCode) = 8

len(@temp_userCode) = 50

Why is the @temp_userCode variable coming back with a length of 50, and how can I trim it back to it's original length to store it correctly?

Further Information:

Running SQL Server 2005, however the solution needs to work in all versions 2005 onwards.

Answer 1

When assigned to CONTEXT_INFO it gets padded out with null bytes 0x00 to 128 bytes in length and becomes 0x5465737455736572000000...

You can use

REPLACE(CAST(CONTEXT_INFO() AS varchar(128)) COLLATE Latin1_General_100_BIN , 
        0x00, 
        '')

Answer 2

It gets padded with CHAR(0). Try:

set @temp_userCode = REPLACE(@temp_userCode COLLATE Latin1_General_BIN, CHAR(0), '');

(EDIT: added an explicit COLLATE clause, though now I feel like I'm stealing from Martin.)