Search code examples
sqldatabasehexvertica

Convert MAC address to integer in Vertica

I want to covert MAC address to integer format in Vertica database. They are in the form like below -

1e:07:02:15:3a:88
1e:07:02:1b:64:ab
...

I used the following query to convert them to integer -

SELECT hex_to_integer(MAC) FROM Network_table;

where MAC is the column name containing MAC addresses and Network_table is the table name.

It is showing me the following error -

Invalid input syntax for numeric: "0x1e:07:02:15:3a:88"

I think its because the address contains ":" symbols. Any idea how can I overcome this problem?

Solution

  • Use regexp_replace() for vertica.

    SELECT hex_to_int(regexp_replace('1e:07:02:15:3a:88',':'))