0

I am trying to convert base64 to Binary in SQL, but I am not getting the proper results I need. The string I am testing is QQ==.

I can convert to varbinary fine, but I need the actual binary output.

Using the following code I get the value 0x41 but I need the value 01000001.

Select CAST(N'' as xml).value('xs:base64Binary("QQ==")','varbinary(20)')

Is it possible to get the exact Binary from base64?

Peter O.
  • 32,158
  • 14
  • 82
  • 96
Caveman42
  • 679
  • 2
  • 13
  • 35
  • I've just added another answer to the duplicated question which I think is a bit simpler than the accepted answer. Please take a look if the accepted answer seems a bit too complicated or overly long. – Tom H Feb 29 '16 at 19:33
  • I was playing around with what you linked me, and this converts INT to binary. Which is fine, since I just used my query above, added a CAST( as int) onto it, and it worked fine. But when it gets into the more complex base64, it just spits back incorrect `ints`. When I plug in "fgA=" it gives me a 32256 when it should be 126. – Caveman42 Mar 02 '16 at 13:33
  • Quick fix I made to this by altering what was going into the conversion to `int` is adding a replace to the `base64` string being passed into the line, to replace all "A" with an "=". `coalesce(REPLACE(base64,'A','='),'AA==')`. This seems to have worked for now, but won't if my base64 gets larger than 2 characters. – Caveman42 Mar 02 '16 at 13:49

0 Answers0