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

martinc123martinc123 Registered User

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);
616263

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) ?

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited July 6

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

    dbadmin=> CREATE TABLE hex (c VARCHAR(3));
    CREATE TABLE
    
    dbadmin=> INSERT INTO hex SELECT 'abc';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> SELECT c, to_hex(c::LONG VARCHAR::LONG VARBINARY) FROM hex;
      c  | to_hex
    -----+--------
     abc | 616263
    (1 row)
    
  • arindamnandidblarindamnandidbl Employee, Registered User

    Varchar to Varbinary casts will be supported from 9.1SP1.

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
  • martinc123martinc123 Registered User

    thanks, any ideas when 9.1SP1 will be released ?

Leave a Comment

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