Actian PSQL/Pervasive SQL: unhex

Question

i have some hex data in db. so i need to unhex it. something like: unhex('414243'). is there any functions or tricks to do it?

i saw unhex function in actian ingres db.

Answer 1

There is no 'unhex' function in Actian PSQL / Actian Zen / Pervasive PSQL as of v15. You might be able to write a function that does the unhex or you can just unhex it once it is returned to the application.

Depending on your data you might have another option. For example if you hex is stored in a Binary field, you might be able to cast it as a char field like:

create table unhex (f1 binary(100), f2 char(100));
insert into unhex values ('61637469616E207073716C0000000000', 'actian psql');
select cast(f1 as char(100)) as bin_to_char, f1, cast(f2 as binary) as char_to_bin, f2 from unhex;

which results in:

bin_to_char                                                                                            f1                                                                                                                                                                                                         char_to_bin                        f2                                                                                                  
====================================================================================================   ========================================================================================================================================================================================================   ================================   ====================================================================================================
actian psql                                                                                            0x61637469616E207073716C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000...   0x61637469616E207073716C2020202020   actian psql