search

sijocherian / google-bigquery

Automatically exported from code.google.com/p/google-bigquery
0 stars 0 forks source link

Convert hex string to hex decimal #228

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
I would like to convert a hex string '0xa1' to actual hex value 0xa1 so that I 
can perform further computations

Original issue reported on code.google.com by tonypart...@geotab.com on 9 Mar 2015 at 3:39

GoogleCodeExporter commented 9 years ago 
Hello,

I found a temporary workaround. For anyone else that encounters the same 
hurdle, see the solution below. 

CASE
WHEN UPPER(HexString) = '00' THEN 0x00
WHEN UPPER(HexString) = '01' THEN 0x01
WHEN UPPER(HexString) = '02' THEN 0x02
WHEN UPPER(HexString) = '03' THEN 0x03
WHEN UPPER(HexString) = '04' THEN 0x04
WHEN UPPER(HexString) = '05' THEN 0x05
WHEN UPPER(HexString) = '06' THEN 0x06
WHEN UPPER(HexString) = '07' THEN 0x07
WHEN UPPER(HexString) = '08' THEN 0x08
WHEN UPPER(HexString) = '09' THEN 0x09
WHEN UPPER(HexString) = '0A' THEN 0x0A
WHEN UPPER(HexString) = '0B' THEN 0x0B
WHEN UPPER(HexString) = '0C' THEN 0x0C
WHEN UPPER(HexString) = '0D' THEN 0x0D
WHEN UPPER(HexString) = '0E' THEN 0x0E
WHEN UPPER(HexString) = '0F' THEN 0x0F
WHEN UPPER(HexString) = '10' THEN 0x10
WHEN UPPER(HexString) = '11' THEN 0x11
WHEN UPPER(HexString) = '12' THEN 0x12
WHEN UPPER(HexString) = '13' THEN 0x13
WHEN UPPER(HexString) = '14' THEN 0x14
WHEN UPPER(HexString) = '15' THEN 0x15
WHEN UPPER(HexString) = '16' THEN 0x16
WHEN UPPER(HexString) = '17' THEN 0x17
WHEN UPPER(HexString) = '18' THEN 0x18
WHEN UPPER(HexString) = '19' THEN 0x19
WHEN UPPER(HexString) = '1A' THEN 0x1A
WHEN UPPER(HexString) = '1B' THEN 0x1B
WHEN UPPER(HexString) = '1C' THEN 0x1C
WHEN UPPER(HexString) = '1D' THEN 0x1D
WHEN UPPER(HexString) = '1E' THEN 0x1E
WHEN UPPER(HexString) = '1F' THEN 0x1F
WHEN UPPER(HexString) = '20' THEN 0x20
WHEN UPPER(HexString) = '21' THEN 0x21
WHEN UPPER(HexString) = '22' THEN 0x22
WHEN UPPER(HexString) = '23' THEN 0x23
WHEN UPPER(HexString) = '24' THEN 0x24
WHEN UPPER(HexString) = '25' THEN 0x25
WHEN UPPER(HexString) = '26' THEN 0x26
WHEN UPPER(HexString) = '27' THEN 0x27
WHEN UPPER(HexString) = '28' THEN 0x28
WHEN UPPER(HexString) = '29' THEN 0x29
WHEN UPPER(HexString) = '2A' THEN 0x2A
WHEN UPPER(HexString) = '2B' THEN 0x2B
WHEN UPPER(HexString) = '2C' THEN 0x2C
WHEN UPPER(HexString) = '2D' THEN 0x2D
WHEN UPPER(HexString) = '2E' THEN 0x2E
WHEN UPPER(HexString) = '2F' THEN 0x2F
WHEN UPPER(HexString) = '30' THEN 0x30
WHEN UPPER(HexString) = '31' THEN 0x31
WHEN UPPER(HexString) = '32' THEN 0x32
WHEN UPPER(HexString) = '33' THEN 0x33
WHEN UPPER(HexString) = '34' THEN 0x34
WHEN UPPER(HexString) = '35' THEN 0x35
WHEN UPPER(HexString) = '36' THEN 0x36
WHEN UPPER(HexString) = '37' THEN 0x37
WHEN UPPER(HexString) = '38' THEN 0x38
WHEN UPPER(HexString) = '39' THEN 0x39
WHEN UPPER(HexString) = '3A' THEN 0x3A
WHEN UPPER(HexString) = '3B' THEN 0x3B
WHEN UPPER(HexString) = '3C' THEN 0x3C
WHEN UPPER(HexString) = '3D' THEN 0x3D
WHEN UPPER(HexString) = '3E' THEN 0x3E
WHEN UPPER(HexString) = '3F' THEN 0x3F
WHEN UPPER(HexString) = '40' THEN 0x40
WHEN UPPER(HexString) = '41' THEN 0x41
WHEN UPPER(HexString) = '42' THEN 0x42
WHEN UPPER(HexString) = '43' THEN 0x43
WHEN UPPER(HexString) = '44' THEN 0x44
WHEN UPPER(HexString) = '45' THEN 0x45
WHEN UPPER(HexString) = '46' THEN 0x46
WHEN UPPER(HexString) = '47' THEN 0x47
WHEN UPPER(HexString) = '48' THEN 0x48
WHEN UPPER(HexString) = '49' THEN 0x49
WHEN UPPER(HexString) = '4A' THEN 0x4A
WHEN UPPER(HexString) = '4B' THEN 0x4B
WHEN UPPER(HexString) = '4C' THEN 0x4C
WHEN UPPER(HexString) = '4D' THEN 0x4D
WHEN UPPER(HexString) = '4E' THEN 0x4E
WHEN UPPER(HexString) = '4F' THEN 0x4F
WHEN UPPER(HexString) = '50' THEN 0x50
WHEN UPPER(HexString) = '51' THEN 0x51
WHEN UPPER(HexString) = '52' THEN 0x52
WHEN UPPER(HexString) = '53' THEN 0x53
WHEN UPPER(HexString) = '54' THEN 0x54
WHEN UPPER(HexString) = '55' THEN 0x55
WHEN UPPER(HexString) = '56' THEN 0x56
WHEN UPPER(HexString) = '57' THEN 0x57
WHEN UPPER(HexString) = '58' THEN 0x58
WHEN UPPER(HexString) = '59' THEN 0x59
WHEN UPPER(HexString) = '5A' THEN 0x5A
WHEN UPPER(HexString) = '5B' THEN 0x5B
WHEN UPPER(HexString) = '5C' THEN 0x5C
WHEN UPPER(HexString) = '5D' THEN 0x5D
WHEN UPPER(HexString) = '5E' THEN 0x5E
WHEN UPPER(HexString) = '5F' THEN 0x5F
WHEN UPPER(HexString) = '60' THEN 0x60
WHEN UPPER(HexString) = '61' THEN 0x61
WHEN UPPER(HexString) = '62' THEN 0x62
WHEN UPPER(HexString) = '63' THEN 0x63
WHEN UPPER(HexString) = '64' THEN 0x64
WHEN UPPER(HexString) = '65' THEN 0x65
WHEN UPPER(HexString) = '66' THEN 0x66
WHEN UPPER(HexString) = '67' THEN 0x67
WHEN UPPER(HexString) = '68' THEN 0x68
WHEN UPPER(HexString) = '69' THEN 0x69
WHEN UPPER(HexString) = '6A' THEN 0x6A
WHEN UPPER(HexString) = '6B' THEN 0x6B
WHEN UPPER(HexString) = '6C' THEN 0x6C
WHEN UPPER(HexString) = '6D' THEN 0x6D
WHEN UPPER(HexString) = '6E' THEN 0x6E
WHEN UPPER(HexString) = '6F' THEN 0x6F
WHEN UPPER(HexString) = '70' THEN 0x70
WHEN UPPER(HexString) = '71' THEN 0x71
WHEN UPPER(HexString) = '72' THEN 0x72
WHEN UPPER(HexString) = '73' THEN 0x73
WHEN UPPER(HexString) = '74' THEN 0x74
WHEN UPPER(HexString) = '75' THEN 0x75
WHEN UPPER(HexString) = '76' THEN 0x76
WHEN UPPER(HexString) = '77' THEN 0x77
WHEN UPPER(HexString) = '78' THEN 0x78
WHEN UPPER(HexString) = '79' THEN 0x79
WHEN UPPER(HexString) = '7A' THEN 0x7A
WHEN UPPER(HexString) = '7B' THEN 0x7B
WHEN UPPER(HexString) = '7C' THEN 0x7C
WHEN UPPER(HexString) = '7D' THEN 0x7D
WHEN UPPER(HexString) = '7E' THEN 0x7E
WHEN UPPER(HexString) = '7F' THEN 0x7F
WHEN UPPER(HexString) = '80' THEN 0x80
WHEN UPPER(HexString) = '81' THEN 0x81
WHEN UPPER(HexString) = '82' THEN 0x82
WHEN UPPER(HexString) = '83' THEN 0x83
WHEN UPPER(HexString) = '84' THEN 0x84
WHEN UPPER(HexString) = '85' THEN 0x85
WHEN UPPER(HexString) = '86' THEN 0x86
WHEN UPPER(HexString) = '87' THEN 0x87
WHEN UPPER(HexString) = '88' THEN 0x88
WHEN UPPER(HexString) = '89' THEN 0x89
WHEN UPPER(HexString) = '8A' THEN 0x8A
WHEN UPPER(HexString) = '8B' THEN 0x8B
WHEN UPPER(HexString) = '8C' THEN 0x8C
WHEN UPPER(HexString) = '8D' THEN 0x8D
WHEN UPPER(HexString) = '8E' THEN 0x8E
WHEN UPPER(HexString) = '8F' THEN 0x8F
WHEN UPPER(HexString) = '90' THEN 0x90
WHEN UPPER(HexString) = '91' THEN 0x91
WHEN UPPER(HexString) = '92' THEN 0x92
WHEN UPPER(HexString) = '93' THEN 0x93
WHEN UPPER(HexString) = '94' THEN 0x94
WHEN UPPER(HexString) = '95' THEN 0x95
WHEN UPPER(HexString) = '96' THEN 0x96
WHEN UPPER(HexString) = '97' THEN 0x97
WHEN UPPER(HexString) = '98' THEN 0x98
WHEN UPPER(HexString) = '99' THEN 0x99
WHEN UPPER(HexString) = '9A' THEN 0x9A
WHEN UPPER(HexString) = '9B' THEN 0x9B
WHEN UPPER(HexString) = '9C' THEN 0x9C
WHEN UPPER(HexString) = '9D' THEN 0x9D
WHEN UPPER(HexString) = '9E' THEN 0x9E
WHEN UPPER(HexString) = '9F' THEN 0x9F
WHEN UPPER(HexString) = 'A0' THEN 0xA0
WHEN UPPER(HexString) = 'A1' THEN 0xA1
WHEN UPPER(HexString) = 'A2' THEN 0xA2
WHEN UPPER(HexString) = 'A3' THEN 0xA3
WHEN UPPER(HexString) = 'A4' THEN 0xA4
WHEN UPPER(HexString) = 'A5' THEN 0xA5
WHEN UPPER(HexString) = 'A6' THEN 0xA6
WHEN UPPER(HexString) = 'A7' THEN 0xA7
WHEN UPPER(HexString) = 'A8' THEN 0xA8
WHEN UPPER(HexString) = 'A9' THEN 0xA9
WHEN UPPER(HexString) = 'AA' THEN 0xAA
WHEN UPPER(HexString) = 'AB' THEN 0xAB
WHEN UPPER(HexString) = 'AC' THEN 0xAC
WHEN UPPER(HexString) = 'AD' THEN 0xAD
WHEN UPPER(HexString) = 'AE' THEN 0xAE
WHEN UPPER(HexString) = 'AF' THEN 0xAF
WHEN UPPER(HexString) = 'B0' THEN 0xB0
WHEN UPPER(HexString) = 'B1' THEN 0xB1
WHEN UPPER(HexString) = 'B2' THEN 0xB2
WHEN UPPER(HexString) = 'B3' THEN 0xB3
WHEN UPPER(HexString) = 'B4' THEN 0xB4
WHEN UPPER(HexString) = 'B5' THEN 0xB5
WHEN UPPER(HexString) = 'B6' THEN 0xB6
WHEN UPPER(HexString) = 'B7' THEN 0xB7
WHEN UPPER(HexString) = 'B8' THEN 0xB8
WHEN UPPER(HexString) = 'B9' THEN 0xB9
WHEN UPPER(HexString) = 'BA' THEN 0xBA
WHEN UPPER(HexString) = 'BB' THEN 0xBB
WHEN UPPER(HexString) = 'BC' THEN 0xBC
WHEN UPPER(HexString) = 'BD' THEN 0xBD
WHEN UPPER(HexString) = 'BE' THEN 0xBE
WHEN UPPER(HexString) = 'BF' THEN 0xBF
WHEN UPPER(HexString) = 'C0' THEN 0xC0
WHEN UPPER(HexString) = 'C1' THEN 0xC1
WHEN UPPER(HexString) = 'C2' THEN 0xC2
WHEN UPPER(HexString) = 'C3' THEN 0xC3
WHEN UPPER(HexString) = 'C4' THEN 0xC4
WHEN UPPER(HexString) = 'C5' THEN 0xC5
WHEN UPPER(HexString) = 'C6' THEN 0xC6
WHEN UPPER(HexString) = 'C7' THEN 0xC7
WHEN UPPER(HexString) = 'C8' THEN 0xC8
WHEN UPPER(HexString) = 'C9' THEN 0xC9
WHEN UPPER(HexString) = 'CA' THEN 0xCA
WHEN UPPER(HexString) = 'CB' THEN 0xCB
WHEN UPPER(HexString) = 'CC' THEN 0xCC
WHEN UPPER(HexString) = 'CD' THEN 0xCD
WHEN UPPER(HexString) = 'CE' THEN 0xCE
WHEN UPPER(HexString) = 'CF' THEN 0xCF
WHEN UPPER(HexString) = 'D0' THEN 0xD0
WHEN UPPER(HexString) = 'D1' THEN 0xD1
WHEN UPPER(HexString) = 'D2' THEN 0xD2
WHEN UPPER(HexString) = 'D3' THEN 0xD3
WHEN UPPER(HexString) = 'D4' THEN 0xD4
WHEN UPPER(HexString) = 'D5' THEN 0xD5
WHEN UPPER(HexString) = 'D6' THEN 0xD6
WHEN UPPER(HexString) = 'D7' THEN 0xD7
WHEN UPPER(HexString) = 'D8' THEN 0xD8
WHEN UPPER(HexString) = 'D9' THEN 0xD9
WHEN UPPER(HexString) = 'DA' THEN 0xDA
WHEN UPPER(HexString) = 'DB' THEN 0xDB
WHEN UPPER(HexString) = 'DC' THEN 0xDC
WHEN UPPER(HexString) = 'DD' THEN 0xDD
WHEN UPPER(HexString) = 'DE' THEN 0xDE
WHEN UPPER(HexString) = 'DF' THEN 0xDF
WHEN UPPER(HexString) = 'E0' THEN 0xE0
WHEN UPPER(HexString) = 'E1' THEN 0xE1
WHEN UPPER(HexString) = 'E2' THEN 0xE2
WHEN UPPER(HexString) = 'E3' THEN 0xE3
WHEN UPPER(HexString) = 'E4' THEN 0xE4
WHEN UPPER(HexString) = 'E5' THEN 0xE5
WHEN UPPER(HexString) = 'E6' THEN 0xE6
WHEN UPPER(HexString) = 'E7' THEN 0xE7
WHEN UPPER(HexString) = 'E8' THEN 0xE8
WHEN UPPER(HexString) = 'E9' THEN 0xE9
WHEN UPPER(HexString) = 'EA' THEN 0xEA
WHEN UPPER(HexString) = 'EB' THEN 0xEB
WHEN UPPER(HexString) = 'EC' THEN 0xEC
WHEN UPPER(HexString) = 'ED' THEN 0xED
WHEN UPPER(HexString) = 'EE' THEN 0xEE
WHEN UPPER(HexString) = 'EF' THEN 0xEF
WHEN UPPER(HexString) = 'F0' THEN 0xF0
WHEN UPPER(HexString) = 'F1' THEN 0xF1
WHEN UPPER(HexString) = 'F2' THEN 0xF2
WHEN UPPER(HexString) = 'F3' THEN 0xF3
WHEN UPPER(HexString) = 'F4' THEN 0xF4
WHEN UPPER(HexString) = 'F5' THEN 0xF5
WHEN UPPER(HexString) = 'F6' THEN 0xF6
WHEN UPPER(HexString) = 'F7' THEN 0xF7
WHEN UPPER(HexString) = 'F8' THEN 0xF8
WHEN UPPER(HexString) = 'F9' THEN 0xF9
WHEN UPPER(HexString) = 'FA' THEN 0xFA
WHEN UPPER(HexString) = 'FB' THEN 0xFB
WHEN UPPER(HexString) = 'FC' THEN 0xFC
WHEN UPPER(HexString) = 'FD' THEN 0xFD
WHEN UPPER(HexString) = 'FE' THEN 0xFE
WHEN UPPER(HexString) = 'FF' THEN 0xFF
END

Original comment by tonypart...@geotab.com on 9 Mar 2015 at 5:25

GoogleCodeExporter commented 9 years ago 
I decided to use integer 64 bits for binary columns

Original comment by tonypart...@geotab.com on 13 Mar 2015 at 2:22