Jayaprakash Nagarajan Jayaprakash Nagarajan - 4 years ago 106
SQL Question

how to grep the string using special character as delimiter in oracle?

I want to fetch the ip addresses which ends before the first special character from the below patterns

SQL> select distinct cell_name from v$cell_state;

CELL_NAME
--------------------------------------------------------------------------------
10.160.0.39;10.160.0.40
10.160.0.41;10.160.0.42
10.160.0.43;10.160.0.44
10.160.0.45;10.160.0.46
10.160.0.47;10.160.0.48
10.160.0.49;10.160.0.50
10.160.0.51;10.160.0.52

expected output:
10.160.0.39
10.160.0.41
10.160.0.43
10.160.0.45
10.160.0.47
10.160.0.49
10.160.0.51

SQL> select distinct cell_name from v$cell_state;

CELL_NAME
--------------------------------------------------------------------------------
10.160.0.39,10.160.0.40
10.160.0.41,10.160.0.42
10.160.0.43,10.160.0.44
10.160.0.45,10.160.0.46
10.160.0.47,10.160.0.48
10.160.0.49,10.160.0.50
10.160.0.51,10.160.0.52

expected output:
10.160.0.39
10.160.0.41
10.160.0.43
10.160.0.45
10.160.0.47
10.160.0.49
10.160.0.51

SQL> select distinct cell_name from v$cell_state;

CELL_NAME
--------------------------------------------------------------------------------
190.160.14.3
190.160.14.4
190.160.14.5
190.160.14.8
190.160.14.9

expected output:
190.160.14.3
190.160.14.4
190.160.14.5
190.160.14.8
190.160.14.9


I want to write a query to achieve it fetches the ip's in all above 3 scenario's Even if there is no special characters then it should fetch the existing ip output

Answer Source

[^;, ] any charter other than ;, , and
* any number of occurrences

select  distinct 
        regexp_substr(cell_name,'[^;, ]*')  

from    v$cell_state
;

Demo

select  regexp_substr('10.160.0.39;10.160.0.40' ,'[^;, ]*')
       ,regexp_substr('10.160.0.39,10.160.0.40' ,'[^;, ]*')
       ,regexp_substr('10.160.0.39'             ,'[^;, ]*')

from    dual
;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download