Divide IPv4 address into 4 numbers in Oracle sql

Question

I am trying to split a given IPv4 address into four numbers.

In SQL Server, this query works well for me:

select CAST (PARSENAME('10.20.30.40',4) AS INT) 

result: 10

 select CAST (PARSENAME('10.20.30.40',3) AS INT) 

result: 20

etc.

I need equivalent syntax in Oracle SQL, but it cannot find it. Any idea?

Answer 1

You can use regexp_substr :

 select ip, regexp_substr(ip, '\d+',1,1) as first_octet, regexp_substr(ip, '\d+',1,2) as second_octet, regexp_substr(ip, '\d+',1,3) as third_octet, regexp_substr(ip, '\d+',1,4) as fourth_octet from (select '10.20.30.40' AS ip from dual )ips; 

Demo version of registries

Answer 2

You can use simple string functions ( INSTR and SUBSTR ), which are much faster than regular expressions:

SQL Fiddle

Oracle 11g R2 schema setup :

 CREATE TABLE sample_data ( ip_address ) AS SELECT '10.20.30.40' FROM DUAL 

Request 1 :

 SELECT TO_NUMBER( SUBSTR( ip_address, 1, first_sep - 1 ) ) AS ClassA, TO_NUMBER( SUBSTR( ip_address, first_sep + 1, second_sep - first_sep ) ) AS ClassB, TO_NUMBER( SUBSTR( ip_address, second_sep + 1, third_sep - second_sep ) ) AS ClassC, TO_NUMBER( SUBSTR( ip_address, third_sep + 1 ) ) AS ClassD FROM ( SELECT ip_address, INSTR( ip_address, '.', 1, 1 ) AS first_sep, INSTR( ip_address, '.', 1, 2 ) AS second_sep, INSTR( ip_address, '.', 1, 3 ) AS third_sep FROM sample_data ) 

Results :

 | CLASSA | CLASSB | CLASSC | CLASSD | |--------|--------|--------|--------| | 10 | 20 | 30 | 40 | 

Answer 3

If you need everything in one function, this is another solution:

 SELECT REGEXP_SUBSTR('10.20.30.40', '\d+', 1, LEVEL) as octet, level FROM dual CONNECT BY LEVEL <= 4; OCTET LEVEL 10 1 20 2 30 3 40 4