Vejam aí o que acham:
create or replace
function Z_get_left_tag
(p_path IN VARCHAR2, p_tag IN VARCHAR2)
RETURN varchar2
IS
v_dir VARCHAR2(2048);
BEGIN
/* Analyst Renato Viana http://br.linkedin.com/pub/renato-menezes-viana/35/573/11b/
------ !!!!!! TEST ONLY !!!!!!!!!
/* Pull the string to the left of the tag. Search starts at the left
SELECT Z_get_left_tag('c:\windows\temp\example.xls', '\temp\example.xls')
FROM dual;
c:\windows
SELECT Z_get_left_tag('c:\windows\temp\example.xls', '\')
FROM dual;
c:
*/
-- strings not null
IF p_path is not null
AND p_tag is not null
AND INSTR (p_path,p_tag,1,1) > 0
AND length(p_tag) < length(p_path) THEN
v_dir := SUBSTR(p_path,1,( INSTR (p_path,p_tag,1,1) - 1 ) );
-- If null return the original string
ELSE
v_dir := p_path;
END IF;
RETURN v_dir;
END;
create or replace
function Z_get_right_tag
(p_path IN VARCHAR2, p_tag IN VARCHAR2)
RETURN varchar2
IS
v_file VARCHAR2(2048);
BEGIN
/* Analyst Renato Viana http://br.linkedin.com/pub/renato-menezes-viana/35/573/11b/
----- !!!!!!!!!!!!!! TEST ONLY !!!!!!!!!!!!!!!!
/* Pull the string to the right of the tag. Search begins in the right
SELECT Z_get_right_tag('c:\temp\example.xls', 'c:\temp\')
FROM dual;
example.xls
SELECT Z_get_right_tag('c:\temp\example.xls', '\')
FROM dual;
example.xls
*/
-- strings no null
IF p_path is not null
AND p_tag is not null
AND INSTR(p_path,p_tag,-1,1) > 0
AND length(p_tag) < length(p_path) THEN
v_file := SUBSTR(p_path,(INSTR(p_path,p_tag,-1,1)+length(p_tag)),length(p_path));
-- If null, return the original string
ELSE
v_file := p_path;
END IF;
RETURN v_file;
END;
SELECT Z_get_left_tag('c:\windows\temp\example.xls', '\temp\example.xls')
FROM dual;
c:\windows
SELECT Z_get_left_tag('c:\windows\temp\example.xls', '\')
FROM dual;
c:
SELECT Z_get_right_tag('c:\temp\example.xls', 'c:\temp\')
FROM dual;
example.xls
SELECT Z_get_right_tag('c:\temp\example.xls', '\')
FROM dual;
example.xls