Search code examples
greenplum

postgresql - Mac Address Regex

I am trying to retrieve all rows with correct mac address using the following query in Greenplum but I also get some rows with junk data like ??:??:??:??:??:??.When I pass the column to another function I get an error

ERROR: "?" is not a valid hexadecimal digit

Here is my select query

select * from table where mac_address like '%:%:%:%:%:%' 
                    and (length(mac_address)) = 17 
                    and mac_address like '^([0-9A-Fa-f]{2}[:-]){5}([0-9A-Fa-f]{2})$'

How can I filter out incorrect mac_addresses from a column in Greenplum?

Solution

  • I found it myself, mac_address ~ '^([0-9A-Fa-f]{2}[:-]){5}([0-9A-Fa-f]{2})$'

    select * from table where mac_address like '%:%:%:%:%:%' 
                    AND (length(mac_address)) = 17 
                    AND mac_address ~ '^([0-9A-Fa-f]{2}[:-]){5}([0-9A-Fa-f]{2})$'
limit 100;