DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Rtrim

  1. #1
    Join Date
    Jun 2003
    Posts
    132

    Rtrim

    Hi,

    Can anyone direct me how to trim this value:

    Example:
    ip address: 199.171.216.254:22808 to 199.171.216.254

    I have above 10,000 record if IP addresses and I need to remove all numbers after (, including (

    Thank you

    Roman

  2. #2
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    SELECT SUBSTR('199.171.216.254:22808 ',1,INSTR('199.171.216.254:22808 ',':')-1) FROM DUAL

    Hope this helps
    Gregg

  3. #3
    Join Date
    Jun 2003
    Posts
    132
    Thanks for the reply,

    However, I have about 10,000 records and it would take me a week to go one by one. Is there another way to do is, possible using REPLACE command?

    Thanks

    Roman

  4. #4
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    Put the values in a table ... run a cursor using the above
    substring command with inst ...

    Gregg

  5. #5
    Join Date
    Jun 2003
    Posts
    132
    I am new at this, would you be so kind and show me the code.

    Thanks

    Roman

  6. #6
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    Are the values already in a table ???

    Gregg

    if so... let's say table_name = ipaddresses

    something to the effect of....

    -- This creates a table with the same structure ... no data

    create table t_ipaddresses as select * from ipaddresses where
    1=2

    --- insert ipaddresses into t_ipaddresses without the last digits;
    --- now you have a table that you can use...

    declare
    v_ip varchar2(50);

    cursor get_ip is
    select ip_address
    from ipaddresses;

    begin
    open get_ip;
    fetch get_ip into v_ip;
    loop
    exit when get_ip%notfound;

    insert into t_ipaddresses
    select SUBSTR(v_ip,1,INSTR(v_ip,':')-1) from dual;

    fetch get_ip into v_ip;

    end loop;
    close get_ip;
    end;
    /


    Hope this helps

    Gregg

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If they're already in a table, perhaps ..

    Code:
    Update
       my_table
    set
       ip_address = SubStr(ip_address,1,InStr(ip_address)-1)
    Where
       InStr(ip_address) > 0
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Jun 2003
    Posts
    132
    Thank you Gbrabham.

    It worked great

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