How can I convert a string that represent a HEX octet in postgresql to binary?

Question

I have a mac adress stored with type mac address in postgresql. I need to convert the first octet in the information about I/G bit and U/L bit (more infos).

Example: mac address is AA-BB-CC-DD-EE-FF. The first Octet is then AA. Converted to Binary: 0101 01010. The last bit represents I/G: 0. The second from right to left is the U/L bit: 1.

How can I do this in a postgresql query?

This is what I have so far:

select id, mac, left(mac::text,2) as octet 
from mytable

The return is:

id,mac,octet
13,aa:XX:XX:XX:XX:XX,aa

I also tried left(mac::text,2)::integer as integer, but I get the error message [22P02] ERROR: invalid input syntax for integer: "aa"

The result I want is the following:

id, mac, octet, binary, ig_bit, ul_bit
13, aa:XX:XX:XX:XX:XX,aa, 1010 1010, 0, 1

So I have isolated the first bit, however, the format is 'text' and I can't convert it to integer or binary.

I found this in the pg mailing list: hex_to_int

clamp
– clamp

2019-03-15 10:40:09 +00:00
Commented Mar 15, 2019 at 10:40 — clamp
– clamp, Commented Mar 15, 2019 at 10:40

klin · Accepted Answer · 2019-03-15 21:42:18Z

1

Use bit strings:

with my_data(mac) as (
    values ('AA-BB-CC-DD-EE-FF'::macaddr)
)

select 
    mac,
    ('x' || left(mac::text, 2))::bit(8) as octet,
    substr(('x' || left(mac::text, 2))::bit(8)::text, 8, 1) as ig_bit,
    substr(('x' || left(mac::text, 2))::bit(8)::text, 7, 1) as ul_bit
from my_data

        mac        |  octet   | ig_bit | ul_bit 
-------------------+----------+--------+--------
 aa:bb:cc:dd:ee:ff | 10101010 | 0      | 1
(1 row)

You can also use the function get_bit(), e.g.:

with my_data(mac) as (
    values ('AA-BB-CC-DD-EE-FF'::macaddr)
),
octet as (
    select mac, ('x' || left(mac::text, 2))::bit(8) as binary_octet
    from my_data
)

select 
    mac,
    binary_octet,
    get_bit(binary_octet, 7) as ig_bit,
    get_bit(binary_octet, 6) as ul_bit
from octet

Note, that the index of the leftmost bit is 0.

edited Mar 15, 2019 at 21:42

answered Mar 15, 2019 at 10:47

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I convert a string that represent a HEX octet in postgresql to binary?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related