title: Encode a string to UTF-8 format
body: <p>I am trying to encode/decode a variable in SQL Server 2012. I am able to decode the variable, but when I was trying to encode it with the decoded value it was not showing the same value. </p>
  
  <pre><code>DECLARE @DecodedVal VARCHAR(500)
  DECLARE @EncodedVal VARCHAR(500) 
  DECLARE @NewDecodeVal VARCHAR(500)
  
   --//decode
  SET @DecodedVal= N'SW5mb3JtYXRpb25zIHN1ciBsZXMgY29uZsOpcmVuY2VzIHTDqWzDqXBob25pcXVlcw=='
  
  SET @EncodedVal = (
      SELECT CONVERT(VARCHAR(2000), CONVERT(XML, '&lt;?xml version="1.0" encoding="UTF-8"?&gt;' + CONVERT(VARCHAR(2000), CONVERT(XML, @DecodedVal).value('.', 'varbinary(max)'))))
      )
  
  SELECT @EncodedVal
  
  --//encode
  SET @NewDecodeVal = (SELECT CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'NVARCHAR(MAX)') Base64Encoding
  FROM (  SELECT CAST(@EncodedVal AS VARBINARY(MAX)) AS bin) AS bin_sql_server_temp)
  
  SELECT @NewDecodeVal 
  </code></pre>