The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Hex value of database varchar field / removing non utf-8 characters

Is there a way how to get hex value of varchar field in vertica?

This works for literal (which is a varchar, no?) :

select to_hex('abc'::VARBINARY);

but this doesn't for db varchar value:

select to_hex(table_name::VARBINARY) from tables
Error: Cannot cast varchar to varbinary

I was eventually able to do it using ascii() function but that only works on 1 character. ie need to use substring and it's not flexible.

I am asking because there were some non UTF-8 bytes/characters loaded into database, and I need to know what hex value they are, as pyodbc driver is throwing errors when reading.

As a secondary question, is there a way of replacing or removing non UTF-8 characters, either before loading ( COPY command options?) or after loading (update column with some SQL functions) ?


  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited July 2018

    Here's how to use TO_HEX on a table's VARCHAR column:

    dbadmin=> CREATE TABLE hex (c VARCHAR(3));
    dbadmin=> INSERT INTO hex SELECT 'abc';
    (1 row)
    dbadmin=> SELECT c, to_hex(c::LONG VARCHAR::LONG VARBINARY) FROM hex;
      c  | to_hex
     abc | 616263
    (1 row)
  • Varchar to Varbinary casts will be supported from 9.1SP1.

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
  • thanks, any ideas when 9.1SP1 will be released ?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file