How can I format a select query

[elentz]

I have a table full of mac addresses in this format 80828704B0EE

I need a method to run a query that will output this format 80:82:87:04:B0:EE

I either need to insert the result of the some query that is formatting the existing mac addresses to another field or use a query to format the mac addresses on the fly.  I need to create a csv file from this query.  I have all that code it is just making the query to format it the way I need it.  Here is a query I found that does the trick except that I get two columns, the original and the formatted one.  How can I remove the mac column that this outputs?

SELECT
    mac, concat(left(mac,2), ":",substring(mac,3,2), ":" , substring(mac,5,2),
     ":",substring(mac,7,2), ":",substring(mac,9,2), ":", 
    right(mac,2)) as mac_formatted 
FROM phones

 

Thanks

[Barand]

Erm, remove it from the select clause?

[Barand]

Or you could always format in php

$mac = '80828704B0EE';
$formatted = join(':', str_split($mac, 2));
echo $formatted;    //--> 80:82:87:04:B0:EE

 

[elentz]

Barand

How would I remove it from the select clause?  Isn't it needed to "source" the unformatted macs?  The end use of the query is to create a csv file, I have that code I just need to get the format correct

 

[Barand]

All you need is

Quote

SELECT
    CONCAT_WS(':', SUBSTR(mac,1,2), SUBSTR(mac,3,2), SUBSTR(mac,5,2), SUBSTR(mac,7,2), SUBSTR(mac,9,2), SUBSTR(mac,11,2))) as mac_formatted
FROM phones;

The "mac" source is specified in eac of the SUBSTR()s

[mac_gyver]

if the NORM is to use the formatted version, you should format the data when it is inserted. if you need the value without the : it is much simpler to remove it when you don't need it.