return the last substring 1

[Ngai88]

Hello,

C:\ftp\pub\I00333\attachments\F002\1095272914500_C:\Documents and Settings\Administrator\Expresso DvplrInWordDoc.doc

In order to extract 'Expresso DvplrInWordDoc.doc', I believe I need to parse a string value and return a substring.

What do you do when it is a case that disregard of how the string is being output, I want to grab the filename that is always behind the last '\' of the string. I would like to make it a function and run it as a trigger.

Any help will be appreciated.

Ngai

 

[lewisp]

Try this:

CREATE OR REPLACE FUNCTION filename (p_str IN VARCHAR2)
RETURN VARCHAR2 IS
  RETURN Substr(p_str,
                Instr(p_str,
                      '\',
                      1,
                      Length(p_str) - Length(Replace(p_str,'\'))) + 1)
END;
/

SELECT filename(your_str)
FROM dual;

 

[sem]

If you pass -1 as the third parameter INSTR returns the last occurence:

substr(<string>, instr(<string>, '\', -1) +1)

Regards, Dima

http://seminihin.narod.ru

 

[Ngai88]

Hi again,
I tried it on SQLPlus, it works. But my question is how come when I turn this into a trigger, it doesn't work? I was hoping that if when I run the java code, it will call the trigger and resolve the problem. It saves me doing the changes inside the java code.
Any idea? If you have any good idea, please help..)o:



Thanks,
Ngai

 

[lewisp]

Can you let us know what the error is and what code you have in your trigger?

 

[Ngai88]

Thanks, it is working now.
CREATE OR REPLACE TRIGGER "ORACLE"."STRIP_FILE_PATH" BEFORE
INSERT ON "XXXTable" FOR EACH ROW begin
select FILENAMEnew.FILE_NAME) into :new.FILE_NAME from dual;
end;

 

[Ngai88]

Hello again,
The triggers work wonderful. How do you go about adding system timestamp to the filename same time as the trigger "Strip_File_Path" is operating?

i.e. 09292004201823_Java.doc

Ngai

 

[Ngai88]

I've decided to split the work by separately adding a column timeStamp to the table instead.

Ngai