-
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
-
SELECT SUBSTR('199.171.216.254:22808 ',1,INSTR('199.171.216.254:22808 ',':')-1) FROM DUAL
Hope this helps
Gregg
-
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
-
Put the values in a table ... run a cursor using the above
substring command with inst ...
Gregg
-
I am new at this, would you be so kind and show me the code.
Thanks
Roman
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|