how to separate IP '103.151.184.6' and store as separate value in SQL

Question

What do you mean by "seperate"? Please show an example of how you want it seperated. Do you mean seperate by dots? What's the purpose of this? — Nick.Mc, May 25 '21 at 04:45
Please show what did you try so far and what was wrong with it. — astentx, May 25 '21 at 06:38
No idea why you like to do this, but you may have a look at this one: https://stackoverflow.com/questions/43221720/oracle-pl-sql-how-to-calculate-range-ip-for-ipv6-cidr/43226773#43226773 — Wernfried Domscheit, May 25 '21 at 07:42
Or do you maybe have a long text message *containing* that IP address and you want to copy that full address into one separate column? A regex may help you find it — Hans Keﬆing, May 25 '21 at 09:45

score 0 · Answer 1 · answered May 25 '21 at 04:48

You should use regexp_substr. Then your code will be look like this.

select 
    regexp_substr('103.151.184.6','[^.]+', 1, level) 
from 
    dual
connect by 
    regexp_substr('103.151.184.6', '[^.]+', 1, level) is not null;

And the output will be this.

score 0 · Answer 2 · answered May 25 '21 at 09:07

You can just get the 1^st - 4^th occurrences of numbers from the string:

SELECT value,
       REGEXP_SUBSTR(value, '\d+', 1, 1) AS ip_value1,
       REGEXP_SUBSTR(value, '\d+', 1, 2) AS ip_value2,
       REGEXP_SUBSTR(value, '\d+', 1, 3) AS ip_value3,
       REGEXP_SUBSTR(value, '\d+', 1, 4) AS ip_value4
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT '103.151.184.6' FROM DUAL;

Outputs:

VALUE IP_VALUE1 IP_VALUE2 IP_VALUE3 IP_VALUE4

103.151.184.6 103 151 184 6

db<>fiddle here

how to separate IP '103.151.184.6' and store as separate value in SQL

2 Answers2