CODEWITHSUNDEEP
sqlpostgresql
SergeyA
SergeyA

Reputation: 62613

Interpret numeric field as a string in SQL

I have a 64-bit integer field in my Postgres database, which is populated with 64 bit integer numbers. (Non) coincidentally, those numbers are actually 8-chars strings in ASCII format, little endian. For example, a number 5208208757389214273 is a numeric representation of a string "ABCDEFGH": it is 0x4847464544434241 in hex, where 0x41 is A, 0x42 is B, 0x43 is C and so forth.

I would like to convert those numbers purely for display purposes - i.e. find a way to leave them as numbers in the database, but be able to see them as strings when querying. Is there any way to do it in SQL? If not in SQL, is there anything I can do on the server side (install extensions, stored procedures, anything at all) which would allow this? This problem is trivially solvable with any script or programming language, but I do not know how to solve it with SQL.

P.S. And just one more time for some of trigger-happy duplicate-hammer-yielding bunch - this is not a question of translating number like 5208208757389214273 to string "5208208757389214273" (we have a lot of answers on how to do this, but this is not what I am looking for).

Upvotes: 1

Views: 268

Answers (1)

sticky bit
sticky bit

Reputation: 37507

Use to_hex() to get a hexadecimal representation for the number. Then use decode() to turn it into a bytea. (Unfortunately I did not find any direct way from bigint to bytea.) Cast that to text and reverse() it, because of the endianess.

reverse(decode(to_hex(5208208757389214273), 'hex')::text)

ABCDEFGH

The bytea_output must be set to 'escape' for this to work properly -- use SET bytea_output = 'escape';.

(Tested on versions 9.4 and 9.6.)

An alternative way to achieve the same rsult without using SET is following:

select reverse(encode(decode(to_hex(5208208757389214273),'hex'),'escape'))

Upvotes: 2

Related Questions