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);616263
but this doesn't for db varchar value:
select to_hex(table_name::VARBINARY) from tablesError: 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
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)Varchar to Varbinary casts will be supported from 9.1SP1.
@arindamnandidbl - Nice!
thanks, any ideas when 9.1SP1 will be released ?