Geek Answers Handbook

Direct binary data cropping in SQL Server

Scenario:

I insert a string into a binary field (CONTEXT_INFO) and then try to pull it out and convert it back to string. When I do this, the resulting string is 128 in length because it has trailing null characters.

Example:

DECLARE @string VARCHAR(128) DECLARE @binary VARBINARY(128) SET @string = 'abcdefg' SET @binary = CONVERT(VARBINARY(128), @string) --0x61626364656667000000... SET CONTEXT_INFO @binary SET @binary = CONTEXT_INFO() -- I would like to change the following line so it trims trailing null chars SET @string = CONVERT(VARCHAR(128), @binary) SELECT @binary AS [binary], DATALENGTH(@binary) AS [binary.Length], --128 as expected @string AS [string], DATALENGTH(@string) AS [string.Length] --This is 128, but I need it to be 7

Question:

How can I truncate trailing null characters when I convert a binary field to a string?

+4
source share
4 answers

Try it, works on Sql-Server 2008. Here is the Sql Fiddle .

Note that I am assuming that the original string does NOT have Char (0), as this can simply replace it even from the original string.

 -- I would like to change the following line so it trims trailing null chars SET @string = CONVERT(VARCHAR(128), @binary) SET @string = REPLACE(@string, Char(0),'')
+4
source

Here is my solution. I went this way because CONTEXT_INFO makes the value look like (pads to the right).

 DECLARE @string VARCHAR(128) DECLARE @binary VARBINARY(128) SET @string = 'abcdefg' SET @string = REPLICATE(CHAR(0),128-LEN(@string)) +@string SET @binary = CONVERT(VARBINARY(128), @string) SET CONTEXT_INFO @binary SET @binary = CONTEXT_INFO() SET @string = CONVERT(VARCHAR(128), REPLACE(@binary,CHAR(0),'')) SELECT @binary AS [binary], DATALENGTH(@binary) AS [binary.Length], @string AS [string], DATALENGTH(@string) AS [string.Length]
+4
source

For the record, this trims the final 0x00 and leave 0x00 in place if they happen in the middle of the data:

 cast(substring(CONTEXT_INFO(), 1, len(REPLACE(REPLACE(CONTEXT_INFO(), 0x20, 0x21), 0x00, 0x20))) as varbinary(128))

This gives the result as varbinary, if you want a string, remove the external listing

Most other solutions either end up removing 0x00 in the middle, or changing it to 0x20.

+1
source

Converting from nvarchar(x) to varbinary(x) back to nvarchar(x) will always be rounded. The main problem described here arises because SELECT CONTEXT_INFO() always returns 128 bytes, regardless of the length of the varbinary source used to set CONTEXT_INFO .

There are two workarounds.

First solution : starting with SQL Server 2016, sp_set_session_context can be used to set significantly superior session_context .

Second workaround : CONTEXT_INFO can be retrieved from sys.dm_exec_sessions using the sys.dm_exec_sessions CONTEXT_INFO() method. This query will return the CONTEXT_INFO associated with the current connection:

 SELECT context_info FROM sys.dm_exec_sessions WHERE session_id = @@SPID

Here is a comparison of two search methods in SSMS:

 SET CONTEXT_INFO 0x010200340056; GO SELECT CONTEXT_INFO(); -- 0x0012003400560000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 SELECT context_info FROM sys.dm_exec_sessions WHERE session_id = @@SPID; -- 0x001200340056 SET CONTEXT_INFO 0x; GO SELECT CONTEXT_INFO(); -- NULL SELECT context_info FROM sys.dm_exec_sessions WHERE session_id = @@SPID; -- 0x

Provision : CONTEXT_INFO setting should be performed in a separate batch, starting from receiving sys.dm_exec_sessions via sys.dm_exec_sessions (as shown above through the SSMS GO packet separator).

0
source

More articles:


All Articles