4

In the Data Explorer, when I do

SELECT CAST( 123456 AS BINARY(4) );

I get an output of:

AAHiQA==

But, according to the docs the output is "usually":

0x0001e240

Applicable query.

I've been looking around, but couldn't find any explanation of the first-mentioned output.

Can someone explain the first-mentioned output and what caused it?

Bonus but slightly off topic:
How I would get the actual hex representation or convert the one to the other?

I originally asked this on StackOverflow, but I was led to believe that this is caused by some intermediate processing (not directly caused by the database) and should thus be here.

Improve this question
6
  • 8
    It's base64 encoding.
    – interjay
    Aug 19, 2013 at 13:07
  • Oops! Accidentally closed as OT because I didn't read closely enough! Fixed!
    – Andrew Barber
    Aug 19, 2013 at 13:07
  • Ah whoops I misled you @AndrewBarber....
    – ಠ_ಠ
    Aug 19, 2013 at 13:08
  • 7
    Slight modification of the title against trigger happy moderators....evil, evil moderators.
    – Bart
    Aug 19, 2013 at 13:08
  • @ಠ_ಠ Bwahaha... I have someone else with whom to share the blame!
    – Andrew Barber
    Aug 19, 2013 at 13:20
  • Where's Tim Stone when we need him? :)
    – Shadow Wizard Is Sad And Angry
    Aug 19, 2013 at 13:25

1 Answer 1

Reset to default
5

As far as the code is concerned, BINARY fields are output as byte[]. Since Data Explorer has no particular reason to specially support this kind of data, it's just passed as-is to the Json.NET serializer, the default behaviour of which is to convert byte arrays to base64-encoded strings.

How I would get the actual hex representation or convert the one to the other?

This is perhaps the more interesting question. What's your use case for wanting Data Explorer to return something different here?

Improve this answer
8
  • This makes perfect sense, Data Explorer shouldn't be used as SQL Server sandbox. Can't you just block any query not selecting from white listed tables? :)
    – Shadow Wizard Is Sad And Angry
    Aug 19, 2013 at 14:03
  • Why not @Sha? It's certainly useful for that and that's what Waffle's initially described it as, "I would like to gather some feedback on the new Sandbox I have been constructing.", see meta.stackexchange.com/questions/49424/…. And Shog9 actually: meta.stackexchange.com/questions/39569/data-dump-sandboxen/…
    – ben is uǝq backwards
    Aug 19, 2013 at 14:05
  • @ben sandbox for Stack Exchange data, not for "pure SQL" stuff like this. There are other sites for this, no? :)
    – Shadow Wizard Is Sad And Angry
    Aug 19, 2013 at 14:06
  • 1
    @benisuǝqbackwards Yeah, I'm personally of the opinion that sites like SQLFiddle are better suited for completely unrelated SQL examples...
    – Tim Stone
    Aug 19, 2013 at 14:12
  • 2
    ...but I'm not really concerned about it to the point where I'd ever want to try and stop people from doing so (even if SE agreed with me) /cc @ShaWizDowArd
    – Tim Stone
    Aug 19, 2013 at 14:12
  • My use case is finding out the actual characters of a comment or a locked post. With a normal post, one can edit-copy-paste, but with a comment or locked post this isn't possible, since some characters are converted for display purposes. In particular, I was looking at this comment, which managed to get new-lines or something which makes it looks like it (which I know isn't supported, although it could be possible).
    – Bernhard Barker
    Aug 19, 2013 at 14:30
  • I discovered that there's a non-standard space character in the code, causing it to not break there. The Unicode-16 0x2007 space character does the same, not sure which character was actually used.
    – Bernhard Barker
    Aug 19, 2013 at 14:47
  • @ShaWizDowArd The available tables are already only a white-list. I'd have to go looking for it, but I remember wanting to query a table / field that was not available (but would definitely be required for a StackExchange site to work).
    – Bernhard Barker
    Aug 19, 2013 at 16:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .