-
query help
hi,
I am trying to modify a query ( which i got from internet searching) to my requirement.
The below query works fine for string '1:3,4:6,8:10,3:4,7:6,11:12
but i am looking for a string of this format,
host1:12.134.23.45,host2:34.56.76.102,..................
I am not familiar with regexp_substr. But still i am working on this.
If somebody can help me, i would greatly appreciate that.
Thanks
sonia
---------------------------------------------
declare
z varchar2(40);
cursor get_vals is
with t as (
select '1:3,4:6,8:10,3:4,7:6,11:12' z from dual
)
select
rtrim(regexp_substr(z,'\d+:',1,level),':') first,
ltrim(regexp_substr(z,':\d+',1,level),':') second
from t
connect by level<=length(z)-length(replace(z,',')) + 1;
begin
for a in get_vals loop
insert into mytable
values (a.first, a.second);
end loop;
commit;
end;
/
-
Code:
SQL> select * from hosts;
HOSTID
-----------------------------------------------------------------------------
host1:12.134.23.45,host2:34.56.76.102
SQL> with t as (
2 select ltrim(regexp_substr(hostid, '[^,]+', 1, level), ',') str
3 from hosts
4 connect by level <= length(hostid)-length(replace(hostid,':')))
5 select regexp_substr(str, '[^:]+') hostname
6 , regexp_substr(str, '[^:]+$') ip
7 from t;
HOSTNAME IP
---------- -------------------
host1 12.134.23.45
host2 34.56.76.102
Last edited by ebrian; 06-12-2010 at 07:33 AM.
-
-
ebrain,
is there any way we can handle if the host name is null or ip address is null without a colon.
ie., something like
host1
or
12.3.4.3
-
This should work:
Code:
SQL> select * from hosts;
HOSTID
----------------------------------------------------------------------
host1:12.134.23.45,host2:34.56.76.102,hosts3,hosts4:10.2.1.0,12.3.4.3,
hosts5.local:198.162.10.1,hosts6.local.com
SQL> with t as (
2 select regexp_substr(hostid, '[^,]+', 1, level) str
3 from hosts
4 connect by level <= length(hostid)-length(replace(hostid,','))+1)
5 select regexp_substr(str, '.*[a-z]+[^:]*',1,1,'i') hostname
6 , ltrim(regexp_substr(str, '(^|:)[^:a-z]+$',1,1,'i'),':') ip
7 from t;
HOSTNAME IP
-------------------- --------------------
host1 12.134.23.45
host2 34.56.76.102
hosts3
hosts4 10.2.1.0
12.3.4.3
hosts5.local 198.162.10.1
hosts6.local.com
7 rows selected.
-
Ebrain,
Thx. It works like a charm.
sorry that i am asking you more.
The query is bit complicated, and whatever i try to moidfy, i am not able to.
Some fileds are coming like host1:12.2.3.4:303
iie., they are coming with port at the end.
I just want to ignore all the data after the second colon ie, igonore 303
Can u pls help me with this modification.
Thanks
sonia
-
Code:
SQL> select * from hosts;
HOSTID
--------------------------------------------------------------------------------
host1:12.134.23.45,host2:34.56.76.102:1521,hosts3,hosts4:10.2.1.0,12.3.4.3:3030,
hosts5.local:198.162.10.1:1521,hosts6.local.com,host7:3000
SQL> with t as (
2 select regexp_substr(hostid, '[^,]+', 1, level) str
3 from hosts
4 connect by level <= length(hostid)-length(replace(hostid,','))+1)
5 select regexp_substr(str, '.*[a-z]+[^:]*',1,1,'i') hostname
6 , ltrim(regexp_substr(str, '(^|:)[0-9]+[.][^:]+'),':') ip
7 from t;
HOSTNAME IP
-------------------- --------------------
host1 12.134.23.45
host2 34.56.76.102
hosts3
hosts4 10.2.1.0
12.3.4.3
hosts5.local 198.162.10.1
hosts6.local.com
host7
8 rows selected.
Last edited by ebrian; 06-24-2010 at 11:54 AM.
-
-
Originally Posted by ebrian
This should work:
Code:
SQL> select * from hosts;
HOSTID
----------------------------------------------------------------------
host1:12.134.23.45,host2:34.56.76.102,hosts3,hosts4:10.2.1.0,12.3.4.3,
hosts5.local:198.162.10.1,hosts6.local.com
SQL> with t as (
2 select regexp_substr(hostid, '[^,]+', 1, level) str
3 from hosts
4 connect by level <= length(hostid)-length(replace(hostid,','))+1)
5 select regexp_substr(str, '.*[a-z]+[^:]*',1,1,'i') hostname
6 , ltrim(regexp_substr(str, '(^|:)[^:a-z]+$',1,1,'i'),':') ip
7 from t;
HOSTNAME IP
-------------------- --------------------
host1 12.134.23.45
host2 34.56.76.102
hosts3
hosts4 10.2.1.0
12.3.4.3
hosts5.local 198.162.10.1
hosts6.local.com
7 rows selected.
Ebrain,
If somebody enters data like host1:IP12.2.22.1 the whole value is taken as host name.
ie anything other than numerical after : is taken as hostname. Is there a way to move it to ip address field .
Thanks
sonia
-
Code:
SQL> select * from hosts;
HOSTID
--------------------------------------------------------------------
host1:12.134.23.45,host2:IP34.56.76.102:1521,hosts3,hosts4:10.2.1.0,
12.3.4.3:3030,hosts5.local:198.162.10.1:1521,hosts6.local.com,host7:
3000
SQL> with t as (
2 select regexp_substr(hostid, '[^,]+', 1, level) str
3 from hosts
4 connect by level <= length(hostid)-length(replace(hostid,','))+1)
5 select regexp_substr(str, '[^:]*[a-z]+[^:]*',1,1,'i') hostname
6 , regexp_substr(str, '\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}') ip
7 from t;
HOSTNAME IP
-------------------- --------------------
host1 12.134.23.45
host2 34.56.76.102
hosts3
hosts4 10.2.1.0
12.3.4.3
hosts5.local 198.162.10.1
hosts6.local.com
host7
8 rows selected.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|