Using Regular expressions in Select 1

[CassidyHunt]

Is there anyway in Oracle 8i to use a regular expression in a select statement?

What I have is a varchar2(80) field that has any number of things in it. However one thing that is common in all of them is a date in YYYY/MM/DD format. I would like to be able to use something that can do substr on that field and only grab the date out of it.

Thank you

Cassidy

 

[SantaMufasa]

Cassidy,

Here is a function to meet your needs:

create or replace function get_date (str_in varchar2) return date is
	slash1_loc	number;
	slash2_loc	number;
	look_at_loc	number;
begin
	look_at_loc	:= 5;
	loop
		slash1_loc	:= instr(str_in,'/',look_at_loc);
		if slash1_loc	= 0 then
			return null;
		end if;
		slash2_loc	:= instr(str_in,'/',look_at_loc,2);
		if slash2_loc-slash1_loc = 3 then
			return to_date
				(substr(str_in,slash1_loc-4,10),'yyyy/mm/dd');
		end if;
		look_at_loc	:= slash1_loc+1;
	end loop;
end;
/

Function created.

select get_date('this // is a test on 2005/02/23. Did it work?') from dual;

23-FEB-05

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 18:51 (23Feb05) UTC (aka "GMT" and "Zulu"),
@ 11:51 (23Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[CassidyHunt]

Worked awesome. Thank you again.

Cassidy