DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: query help

  1. #1
    Join Date
    Feb 2002
    Posts
    267

    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;
    /


  2. #2
    Join Date
    Apr 2006
    Posts
    377
    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.

  3. #3
    Join Date
    Feb 2002
    Posts
    267
    amazing.

    thx ebrain.

  4. #4
    Join Date
    Feb 2002
    Posts
    267
    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

  5. #5
    Join Date
    Apr 2006
    Posts
    377
    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.

  6. #6
    Join Date
    Feb 2002
    Posts
    267
    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

  7. #7
    Join Date
    Apr 2006
    Posts
    377
    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.

  8. #8
    Join Date
    Feb 2002
    Posts
    267
    Thx ebrain.

  9. #9
    Join Date
    Feb 2002
    Posts
    267
    Quote Originally Posted by ebrian View Post
    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

  10. #10
    Join Date
    Apr 2006
    Posts
    377
    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
  •  


Click Here to Expand Forum to Full Width