CODEWITHSUNDEEP
sql-serverencodingbase64
Techie
Techie

Reputation: 1491

TSQL - Base64 Encoding Issues - text v/s column

After referring to the question Base64 encoding in SQL Server 2005 T-SQL, I tried to get the base64 values for some data from sql table but it's not giving proper values when compared to direct text values.

Using Direct text:

SELECT CAST('?' as varbinary) FOR XML PATH(''), BINARY BASE64

gives value as Pw== which is correct and it decodes to ?

Using Database entry:

SELECT CAST([Col] as varbinary) from tblTable FOR XML PATH(''), BINARY BASE64

with [Col] value = ?, gives output as PwA= which when decoded gives ? and an extra non-printable character.

Screenshot when checked using len function.

enter image description here

The reason for this is that I want to convert data for few columns from plain text to base64 using update statement, this is just sample value and actual values are bigger text which is also having this problem but with every character.

Edit: This when decoded from ASP.Net, if it's on label then it displays properly but when used in textbox shows extra junk characters.

Upvotes: 0

Views: 1516

Answers (1)

Liesel
Liesel

Reputation: 2979

Two things:

First, the "Direct Text" example:

SELECT CAST('?' as varbinary) FOR XML PATH(''), BINARY BASE64

----
Pw==

Is encoding the single byte (VARCHAR) character to base 64.

For an NVARCHAR, a 2 bytes per character type, it should be this:

SELECT CAST(N'?' as varbinary) FOR XML PATH(''), BINARY BASE64

----
PwA=

In the sceond part of your question, asking why there is an extra character produced during decoding of your previously encoded NVARCHAR type column. You're actually taking the 2 bytes encoded as base 64 and converting them to 2 single byte (VARCHAR) characters.

In order to decode to NVARCHAR you need to do this:

SELECT CAST(CAST( 'PwA=' as XML ).value('.','varbinary(max)') AS NVARCHAR(250) )

---
?

Upvotes: 2

Related Questions