Jump to content
elentz

How can I format a select query

Recommended Posts

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

Share this post


Link to post
Share on other sites

Or you could always format in php

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

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.