1

I have some base64 string (I may be wrong if this is any other format) that I want to convert to anything that is in readable format or has some meaning in sql server.

0x50340000432F4F205542532046494E414E4349414C202020202020202020202020202020202020353036352057455354544845494D4552205354452031303030202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020484F5553544F4E2020202020202020202020202020202020545837373035363636363820202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202000000000000000000000202020202020202020200000000000000000000000000000000000000000000000000000000000000000

I gone through the following question(Base64 encoding in SQL Server 2005 T-SQL) and its response but unable to convert it.

halfer
  • 19,824
  • 17
  • 99
  • 186
LogicalDesk
  • 1,237
  • 4
  • 16
  • 46

1 Answers1

4

That isn't Base64, it's just a hexadecimal notated number. You can convert it to varbinary and from there to varchar.

SELECT convert(varchar(max), convert(varbinary(max), '0x50340000432F4F205542532046494E414E4349414C202020202020202020202020202020202020353036352057455354544845494D4552205354452031303030202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020484F5553544F4E2020202020202020202020202020202020545837373035363636363820202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202000000000000000000000202020202020202020200000000000000000000000000000000000000000000000000000000000000000', 1));

It will get you: "P4C/O UBS FINANCIAL 5065 WESTTHEIMER STE 1000 HOUSTON TX770566668 "

SQL Fiddle

Edit: If you're using SSMS, it seems like that doesn't like the null bytes/characters in the result. It seems to see them as string terminators and cuts the result off at their position. As a workaround you can try to remove the null bytes from the input string.

SELECT convert(varchar(max), convert(varbinary(max), replace('0x50340000432F4F205542532046494E414E4349414C202020202020202020202020202020202020353036352057455354544845494D4552205354452031303030202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020484F5553544F4E2020202020202020202020202020202020545837373035363636363820202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202000000000000000000000202020202020202020200000000000000000000000000000000000000000000000000000000000000000', '00', ''), 1));
sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • I'm just getting P4 when I execute the same query provided by you. But when I execute on the fiddle, it is returning everything. – LogicalDesk Jul 31 '19 at 10:18
  • I'm using sql server 2014 and it is not working, only returning P4. Please let me know what changes I need to do in this script to get desired result. – LogicalDesk Jul 31 '19 at 11:58
  • @LogicalDesk: I don't know. As you see in the fiddle it works. You haven't changed or omitted the `(max)` by any chance? If you did, that's probably the issue. – sticky bit Jul 31 '19 at 12:16
  • sticky, I haven't changed any part of it and I'm using the same query by just copy/paste but on sql server 2014 it is just returning P4 :( – LogicalDesk Jul 31 '19 at 12:19
  • @LogicalDesk: Maybe a client problem? Which client do you use? And what do you get when you issue the following: – sticky bit Jul 31 '19 at 12:25
  • `SELECT len(convert(varchar(max), convert(varbinary(max), '0x50340000432F4F205542532046494E414E4349414C202020202020202020202020202020202020353036352057455354544845494D4552205354452031303030202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020484F5553544F4E2020202020202020202020202020202020545837373035363636363820202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202000000000000000000000202020202020202020200000000000000000000000000000000000000000000000000000000000000000', 1)));` – sticky bit Jul 31 '19 at 12:25
  • sticky, I'm using SSMS 2008r2 and I'm getting '247' count. – LogicalDesk Jul 31 '19 at 12:29