How do I get a string value from its hex in PostgreSQL?

Question

I have a string value of '2.4.3369.6'. Using the command: select encode('2.4.3369.6', 'hex') as string_to_hex

I translate this value into a hex and get the next value of

'322e342e333336392e36'

Which command or commands do I use to get the value '2.4.3369.6' from the value '322e342e333336392e36'?

In case anyone's wondering, decode('322e342e333336392e36','hex') doesn't work. — Bohemian
– Bohemian ♦, Commented Jun 26, 2018 at 9:15
Everything works, thank you. Just my editor (pgadmin) why that values were not displayed! — Anton
– Anton, Commented Jun 26, 2018 at 9:18
select '2.4.3369.6' as string_in_dec, encode('2.4.3369.6', 'hex') as string_to_hex, convert_from(decode('322e342e333336392e36','hex'),'UTF-8') as string_to_dec; — Anton
– Anton, Commented Jun 26, 2018 at 9:28

Laurenz Albe · Accepted Answer · 2018-06-26 09:23:04Z

7

Assuming that the encoding is UTF8, you could use the following to get it as text:

SELECT convert_from(decode('322e342e333336392e36','hex'), 'UTF8');

┌──────────────┐
│ convert_from │
├──────────────┤
│ 2.4.3369.6   │
└──────────────┘
(1 row)

answered Jun 26, 2018 at 9:23

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Y Y Over a year ago

Is there a postgres 12 compatible function for this ? I have scoured through the docs to no avail. Thanks!

Laurenz Albe Over a year ago

@YY That works fine in v12, I just tried.

Collectives™ on Stack Overflow

How do I get a string value from its hex in PostgreSQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related