help convert mac address string to hex [message #666797] |
Sun, 26 November 2017 05:48 |
|
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 |
Solomon Yakobson
Messages: 3275 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 #666803 is a reply to message #666802] |
Sun, 26 November 2017 09:05 |
Solomon Yakobson
Messages: 3275 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 #666805 is a reply to message #666801] |
Sun, 26 November 2017 14:16 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
laredoeneko wrote on Sun, 26 November 2017 08:21Thank 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.
|
|
|