Extracting just part of a field´s data

[tmf33uk]

Hi!

I need to select from a field just part of a character string. The field, specifically is comment_text from dba_audit_trail, and contains:

Authenticated by: OS; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.203.31)(PORT=59609))

I´m only interested in the IP address from each row, but I can´t think of a way to extract this. Can anybody please point me to the right function?

Thanks!

T.

 

[jimirvine]

Hi, you can use a combination of instr and substr to do this.
use instr to identify the position of 'HOST=' this value +6 will give you the starting position for the ip address value, you can the search for 'PORT=' which will give you end pos and from that you can calulate the number of characters that you meed for the 3rd argument of substr.

 

[sandtek]

try playing with string manipulation functions: (substr and instr)

set serveroutput on size 1000000
declare
	v_row1 		varchar2 (512) := 'Authenticated by: OS; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.203.31)(PORT=59609))';
	v_ip		varchar2 (20);	
begin 	
	select substr (v_row1, 
		       instr(v_row1, 'HOST=')+5, 
		       length (v_row1) - ((instr(v_row1, 'HOST=')+5)+(length (v_row1)-instr(v_row1, ')', instr(v_row1, 'HOST=')+5))))
	into 	v_ip
	from dual;
	
	dbms_output.put_line (v_ip);	
end;
/

set serveroutput on size 1000000
declare
v_row1 varchar2 (512) := 'Authenticated by: OS; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.203.31)(PORT=59609))';
v_ip varchar2 (20);
begin
select substr (v_row1,
instr(v_row1, 'HOST=')+5,
length (v_row1) - ((instr(v_row1, 'HOST=')+5)+(length (v_row1)-instr(v_row1, ')', instr(v_row1, 'HOST=')+5))))
into v_ip
from dual;

dbms_output.put_line (v_ip);
end;
/

Hope I answered your post... ;-)

 

[sandtek]

sorry for the duplicate code below the code box. please ignore it.. ;-)

 

[HarleyQuinn]

Another way (not sure of performance as I've only ever used it on small datasets) you could also use is REGEXP_SUBSTR() looking for a pattern that matches an IP address.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.