IFS_PATH sql scalar function

With all the nice IFS sql functions like IFS_READ and IFS_WRITE it becomes more importent to work with the "path" .

I'll suggest a IFS_PATH scalar function to handle the segments of an path:

It could be somthing like this:

-- Returns the path, filename, extension or file with extension
-- section of a IFS filename 
--
-- Simply paste this gist into ACS SQL and step through the example.
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
--
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2024
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either express or implied.
----------------------------------------------------------------------------------------------
create or replace function qusrsys.ifs_path (
    path_name varchar(256),
    section   varchar(20) 
)
  returns varchar(256) 
  deterministic
  no external action
  reads sql data
  not fenced
  set option COMMIT = *NONE
begin
    case 
      when section = 'FILE_ONLY' then
        return ifnull(regexp_substr(path_name , '([^\\/]+)(?=\.[^\\/]+$)'),'');
      when section = 'EXTENSION_ONLY' then 
        return ifnull(regexp_substr(path_name , '(\w+)$'),'');
      when section = 'PATH_ONLY' then 
        return ifnull(regexp_substr(path_name , '^.*[\\/]', 1, 1),'');
      when section = 'FILE_NAME' then 
        return ifnull(regexp_substr(path_name , '[^\\/]+$', 1, 1),'');
      else 
        return path_name;
    end case;
end;

values qusrsys.ifs_path ('/home/john/myfile.txt' , 'FILE_ONLY');
values qusrsys.ifs_path ('/home/john/myfile.txt' , 'EXTENSION_ONLY');
values qusrsys.ifs_path ('/home/john/myfile.txt' , 'PATH_ONLY');
values qusrsys.ifs_path ('/home/john/myfile.txt' , 'FILE_NAME');

values qusrsys.ifs_path ('myfile.txt' , 'FILE_ONLY');
values qusrsys.ifs_path ('myfile.txt' , 'EXTENSION_ONLY');
values qusrsys.ifs_path ('myfile.txt' , 'PATH_ONLY');
values qusrsys.ifs_path ('myfile.txt' , 'FILE_NAME');

values qusrsys.ifs_path ('/' , 'FILE_ONLY');
values qusrsys.ifs_path ('/' , 'EXTENSION_ONLY');
values qusrsys.ifs_path ('/' , 'PATH_ONLY');
values qusrsys.ifs_path ('/' , 'FILE_NAME');
   

The implementation can be found here:

https://gist.github.com/NielsLiisberg/469fad64194ddb60ac7fdc32d9a3fa74