Mike wrote:
Firebird v1.5.3 and v2.5.4
I need a way to extract just the file name for a given file path either by code in a stored procedure or by calling a UDF in the stored procedure.
Example, I need to extract "SNKSAid.dat" from "KFrontlineDocumentsAidsSNKSAid.dat"
Mark Rotteveel answers:
For Firebird 2.5 it is simpler than for Firebird 1.5. In Firebird 2.5 you can do:
EXECUTE BLOCK RETURNS (filename VARCHAR(1024))
AS
DECLARE filepath VARCHAR(1024);
DECLARE searchIndex int;
DECLARE previousIndex int;
BEGIN
filepath = 'K:\Frontline\Documents\Aids\SNKSAid.dat';
previousIndex = 0;
searchIndex = position('\', filepath);
WHILE (searchIndex > 0) DO
BEGIN
previousIndex = searchIndex;
searchIndex = position('\', filepath, previousIndex + 1);
END
filename = substring(filepath from previousIndex + 1);
SUSPEND;
END
For Firebird 1.5, you would need to find equivalent UDFs for position and substring (and of course you can't use execute block in 1.5, but I only used it to demonstrate the solution).
Rajiv D.S. Chauhan answers:
SET TERM ^ ;
create ExtractFileName(
FILEPATH varchar(360) DEFAULT 'D:\Program
Files\Firebird\Firebird_2_5\firebird.log' )
RETURNS (
FILENAME varchar(360) )
AS
declare variable Loop_Break smallint default 0;
declare variable POS_BK_SLASH smallint default 0;
declare variable pathlength smallint default 0;
BEGIN
Loop_Break = 0;
while(0 = :Loop_Break) do begin
select position('\' in :FilePath) from RDB$DATABASE into
:POS_BK_SLASH;
if(0 != :POS_BK_SLASH) then begin
pathlength = char_length(FilePath);
FileName = substring(:FilePath from :POS_BK_SLASH+1 for
:pathlength - :POS_BK_SLASH + 1);
FilePath = :FileName;
end else begin
Loop_Break = 1;
end
end
suspend;
END^
SET TERM ; ^