3

I have a mysql database with a macaddress column, this contains thousands of mac addresses in various formats; uppercase, lowercase and with colons. See below for an example;

+-------------------+
| macaddress        |
+-------------------+
| 90CCAADD3341      |
+-------------------+
| 90:3f:ff:11:22:33 |
+-------------------+
| 33:44:aa:bb:34:6a |
+-------------------+
| 8801abcd3231      |
+-------------------+

Is there any way I can bulk update all rows in this column so they are in the same format?

I'd like them all to be uppercase with no colons, see below;

+--------------+
| macaddress   |
+--------------+
| 90CCAADD3341 |
+--------------+
| 903FFF112233 |
+--------------+
| 3344AABB346A |
+--------------+
| 8801ABCD3231 |
+--------------+

How can I achieve this?

2

Test select:

select macaddress, upper(replace(macaddress,':','')) as new_macaddress
from Macs;

Update:

update macs
set macaddress = upper(replace(macaddress,':',''));

Fiddle

Test data:

create table macs
(
macaddress varchar(20)
);

insert into macs values('90CCAADD3341');

insert into macs values('90:3f:ff:11:22:33');

insert into macs values('33:44:aa:bb:34:6a');

insert into macs values('8801abcd3231');

Documentation links:

UPPER()

REPLACE()

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.