Hex in Netezza

I needed to convert Hex strings to integers in Netezza. Here is how I did it:

Put a file called hex2dec.py at /nz/export/ae/applications/system/admin/hex2dec.py

with contents:

import nzae

class Hex2DecUdfAe(nzae.Ae):

    def _getFunctionResult(self, row):

        hexString, base = row

        return int(hexString, base)

Hex2DecUdfAe.run()

then register it with

[nz@dw01 ~/scripts]/nz/export/ae/utilities/bin/register_ae --language python --version 1 --template udf --exe hex2dec.py --sig "hex2dec(varchar(255), int4)" --return int4

Call it like this:

select system..hex2dec('101', 2), system..hex2dec('A1', 16);