Home » SQL & PL/SQL » SQL & PL/SQL » help convert mac address string to hex (oracle 12c)
help convert mac address string to hex [message #666797] Sun, 26 November 2017 05:48 Go to next message
laredoeneko
Messages: 21
Registered: November 2017
Junior Member
Hello
i have a column with the following format:
0.74.119.236.207.145
72.141.54.178.169.110
224.81.99.183.138.143
they are mac addres in decimal format, i need the output in classical format
0.74.119.236.207.145 -> 0.4a.77.ec.cf.91

please can you help me to get an oracle function or something similar?
best regard


Re: help convert mac address string to hex [message #666799 is a reply to message #666797] Sun, 26 November 2017 07:28 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
WITH T AS (
           SELECT '0.74.119.236.207.145' DEC FROM DUAL UNION ALL
           SELECT '72.141.54.178.169.110' FROM DUAL UNION ALL
           SELECT '224.81.99.183.138.143' FROM DUAL UNION ALL
           SELECT '0.74.119.236.207.145' FROM DUAL
          )
SELECT  DEC,
        TO_CHAR(REGEXP_SUBSTR(DEC,'[^.]+',1,1),'FMXX') || '.' ||
        TO_CHAR(REGEXP_SUBSTR(DEC,'[^.]+',1,2),'FMXX') || '.' ||
        TO_CHAR(REGEXP_SUBSTR(DEC,'[^.]+',1,3),'FMXX') || '.' ||
        TO_CHAR(REGEXP_SUBSTR(DEC,'[^.]+',1,4),'FMXX') || '.' ||
        TO_CHAR(REGEXP_SUBSTR(DEC,'[^.]+',1,5),'FMXX') || '.' ||
        TO_CHAR(REGEXP_SUBSTR(DEC,'[^.]+',1,6),'FMXX') HEX
  FROM  T
/

DEC                   HEX
--------------------- -----------------------
0.74.119.236.207.145  0.4A.77.EC.CF.91
72.141.54.178.169.110 48.8D.36.B2.A9.6E
224.81.99.183.138.143 E0.51.63.B7.8A.8F
0.74.119.236.207.145  0.4A.77.EC.CF.91

SQL> 

SY.
Re: help convert mac address string to hex [message #666800 is a reply to message #666799] Sun, 26 November 2017 07:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Or use xx instead of XX if you want HEX digits in lower case.

SY.
Re: help convert mac address string to hex [message #666801 is a reply to message #666800] Sun, 26 November 2017 08:21 Go to previous messageGo to next message
laredoeneko
Messages: 21
Registered: November 2017
Junior Member
Thank you very much, how can i do It there IS 2 or more Mac address for field?
Re: help convert mac address string to hex [message #666802 is a reply to message #666801] Sun, 26 November 2017 08:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
post a sample.

SY.
Re: help convert mac address string to hex [message #666803 is a reply to message #666802] Sun, 26 November 2017 09:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
WITH T AS (
           SELECT '0.74.119.236.207.145, 72.141.54.178.169.110, 224.81.99.183.138.143, 0.74.119.236.207.145' DEC FROM DUAL
          )
SELECT  XMLCAST(
                XMLQUERY(
                         '/ROWSET/ROW/HEX'
                         PASSING DBMS_XMLGEN.GETXMLTYPE(
                                                        'SELECT  TO_CHAR(' ||
                                                        REPLACE(
                                                                REPLACE(
                                                                        REPLACE(
                                                                                DEC,
                                                                                ',',
                                                                                Q'[,'FMXX') || ',' || TO_CHAR(]'
                                                                               ),
                                                                        '.',
                                                                        '. || TO_CHAR('
                                                                       ),
                                                                '.',
                                                                Q'[,'FMXX') || '.' ]'
                                                               ) || Q'[,'FMXX') HEX
                                                           FROM  DUAL]'
                                                       )
                         RETURNING CONTENT
                        )
                AS VARCHAR2(4000)
               ) HEX
  FROM  T
/

HEX
----------------------------------------------------------------------
0.4A.77.EC.CF.91,48.8D.36.B2.A9.6E,E0.51.63.B7.8A.8F,0.4A.77.EC.CF.91

SQL> 

SY.
Re: help convert mac address string to hex [message #666804 is a reply to message #666803] Sun, 26 November 2017 09:13 Go to previous messageGo to next message
laredoeneko
Messages: 21
Registered: November 2017
Junior Member
Fantastic thank you very much!!!!
Re: help convert mac address string to hex [message #666805 is a reply to message #666801] Sun, 26 November 2017 14:16 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
laredoeneko wrote on Sun, 26 November 2017 08:21
Thank you very much, how can i do It there IS 2 or more Mac address for field?
If by "field" you mean "column", then you do it by fixing your flawed design. Storing more than one value in a column violates First Normal Form.
Previous Topic: Array ORA-06533: Subscript beyond count
Next Topic: Using views to summarise payment info
Goto Forum:
  


Current Time: Thu Mar 28 20:39:37 CDT 2024