0

When I'm executing the following query:

SELECT HEX(FILL),ID FROM dbo.table WHERE FILL <> ''

It gives me below result:

404040404040404000000F40404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040

When I modified my query like this 

SELECT FILL,ID FROM dbo.table WHERE FILL <> ''

I'm getting the result as shown in the picture:

enter image description here

used this URL for conversion: https://codebeautify.org/hex-string-converter

The above URL does not give me correct desired result. So I used, Python.

This is my python code to convert to a readable format:

import base64
hex_data   ='404040404040404000000F40404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040'
ascii_string = str(base64.b16decode(hex_data))[2:-1]
print (ascii_string)

I'm getting the result as:

@@@@@@@@\x00\x00\x0f@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

How can I make the string readable ( I wanted to read plain English chars)?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
AskMe
  • 2,495
  • 8
  • 49
  • 102
  • What is the output of that text supposed to be? – FcoRodr Oct 31 '17 at 09:32
  • We are getting data from IBM DB2 iSeries machine and reading those for Analytics. So, we are excepting it to be a numbers or chars. – AskMe Oct 31 '17 at 09:35
  • The image you uploaded appears to be a large blank rectangle with a small square in the top-left corner that says `FILL`, together with 3 1/2 rows of placeholder squares. – Martijn Pieters Oct 31 '17 at 10:13
  • People are down voting this. However, I can challenge Python exports to at least give a proper clue, at least; as Python has been most popular language to analyze any data (I think even Data from Galaxy has been analyzed by Python). They may give the solution or not. If I got a solution, I'll definitely share here. – AskMe Nov 01 '17 at 03:59

2 Answers2

0

You mangled the bytes value by passing it to str(). By passing your bytes object through str() here, you produced a representation of the bytes object. That a representation is a string too is coincidental.

You can, at best, decode the bytes to text with the decode method:

bytes_object = base64.b16decode(hex_data)
text_object = bytes_object.decode('latin1')

I used Latin-1 (ISO-8859-1) to decode the data, because it is probably not ASCII data. Most ASCII text doesn't use NULL or SI (shift-in) bytes, to begin with. ASCII will work too, but as soon as you have any data outside of the 0x00-0x7F range you'll get an error instead.

The hex string converter you found includes the NULL and SI bytes too, but they are not printable so your browser doesn't show them. If you use print() then most likely your terminal won't show them either:

>>> import base64
>>> hex_data   ='404040404040404000000F40404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040'
>>> bytes_object = base64.b16decode(hex_data)
>>> text_object = bytes_object.decode('latin1')
>>> text_object
'@@@@@@@@\x00\x00\x0f@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
>>> print(text_object)
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
-1

Try binascii.unhexlify():

>>> binascii.unhexlify('404040404040404000000F40404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040')
'@@@@@@@@\x00\x00\x0f@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'

Of course, you could also change your query to not hex encode the field:

SELECT FILL,ID FROM dbo.table WHERE FILL <> ''

which seems a better solution.

mhawke
  • 84,695
  • 9
  • 117
  • 138
  • I have just modifies the question as per your suggestion and the data in a screenshot is shown. Please have a look at the data in the screenshot and suggest. I wanted to read plain English chars.Thanks. – AskMe Oct 31 '17 at 09:26
  • The screenshot doesn't really help. The string contains `\x00` (NUL) and '\x0F` characters which are not printable, and that might explain the screenshot. What is the value supposed to be? It looks like those values are actually in the database. Try querying with your databases' native user interface. – mhawke Oct 31 '17 at 09:42
  • `unhexlify` produces the same output `b16decode`. They are using Python 3, and have mangled the `bytes` object. – Martijn Pieters Oct 31 '17 at 09:51