1

Can't decode turkish characters in base64 string.

Base64 string = "xJ/DvGnFn8Onw7bDlsOHxLDEnsOcw5w="

When I decode it must be like this : 'ğüişçöÖÇİĞÜÜ' I try to decode like this :

SELECT CAST(
          CAST(N'' AS XML).value('xs:base64Binary("xJ/DvGnFn8Onw7bDlsOHxLDEnsOcw5w=")' , 'VARBINARY(MAX)') 
          AS NVARCHAR(MAX)
           ) UnicodeEncoding ;

Based on this answer : Base64 encoding in SQL Server 2005 T-SQL

But have response like this : '鿄볃앩쎟쎧쎶쎖쒇쒰쎞쎜'

Base64 string is correct because when I try decode in Base64decode.org it works.

Is there any way to decode turkish characters?

Community
  • 1
  • 1
GeoVIP
  • 1,524
  • 9
  • 25
  • 45
  • Why would you need to decode base-64 on the SQL server? Just pass it as a ready `nvarchar` and be done with it. Is there a reason why you want base-64 in the database? It's bigger, it's clunkier, it can't use indices... And the answer you're referring to doesn't handle different character sets, you can't fix it by just casting to `nvarchar` instead of `varchar` :) – Luaan Aug 04 '14 at 07:52
  • we have backend in erlang and there cant decode base64 unicode string and i think one of solutions is to decode in database. I have problems only with unicode – GeoVIP Aug 04 '14 at 07:56
  • But why do you want to have the data in Base-64 in the first place? Why not simply use unicode strings and `nvarchar`? Erlang *does* support unicode strings. – Luaan Aug 04 '14 at 07:57
  • we decode to base64 because we have sometime problems in erlang with unicode – GeoVIP Aug 04 '14 at 08:01
  • That sounds like you're doing something wrong with Erlang and/or Unicode. Which is quite likely if you're converting unicode strings to binary and back. You might want to fix your code issues in the back-end, instead of trying to hack a solution on the DB :) Your input binary seems to be encoded in UTF-8 - MS SQL doesn't really support UTF-8 at all. If you encode in UTF-16 instead, the code should work fine (although it's obviously rather slow). Oh, and when you're already working with binary, why not send the data as `varbinary` instead of base-64? – Luaan Aug 04 '14 at 08:04
  • Can you give me code example how convert to utf-16 mydata? – GeoVIP Aug 04 '14 at 08:09
  • I don't do Erlang, sorry :) Unicode is quite a complex encoding, so I'm affraid there isn't a simple solution to code on the MS SQL side. There should be a way to do that on the back-end, though. Although that would probably also mean you'd fix the mistake you made to need to send the data as base-64 in the first place, I guess :D – Luaan Aug 04 '14 at 08:11

1 Answers1

0

Your base-64 encoded data contains an UTF-8 string. MS SQL doesn't support UTF-8, only UTF-16, so it fails for any characters outside of ASCII.

The solution is to either send the data as nvarchar right away, or to encode the string as UTF-16 (and send it as varbinary or base-64, as needed).

Based on Erlang documentation, this might require an external library, unicode: http://www.erlang.org/doc/apps/stdlib/unicode_usage.html

Basically, the default seems to be UTF-8, you need to specify UTF-16 manually. UTF-16 support seems a bit clunky, but it should be quite doable.

Luaan
  • 62,244
  • 7
  • 97
  • 116