-
-
Save forstie/98593cc15e771079154441287d4c75a9 to your computer and use it in GitHub Desktop.
-- | |
-- Subject: Extracting the IFS file name from a path, using regular expression built-in functions | |
-- Author: Scott Forstie | |
-- Date : September 27, 2021 | |
-- Features Used : This Gist uses regexp_count, regexp_instr, substr, and SQL PL | |
-- | |
-- Function - The request was, if you have an absolute path, how can SQL extract the filename from the path? | |
-- One approach is found below. | |
-- | |
-- | |
values regexp_count('/usr/scottf/subdir1/subdir2/subdir3/anifsfile.sfx', '/'); | |
-- | |
-- Returns: | |
-- 6 | |
stop; | |
values regexp_instr( | |
'/usr/scottf/subdir1/subdir2/subdir3/anifsfile.sfx', '/', 1, | |
regexp_count('/usr/scottf/subdir1/subdir2/subdir3/anifsfile.sfx', '/')); | |
-- | |
-- Returns: | |
-- 36 | |
-- | |
-- '/usr/scottf/subdir1/subdir2/subdir3/anifsfile.sfx', '/', 1, | |
-- 123456789012345678901234567890123456 | |
-- | |
stop; | |
-- | |
-- Put it all together | |
-- | |
values substr('/usr/scottf/subdir1/subdir2/subdir3/anifsfile.sfx', | |
regexp_instr('/usr/scottf/subdir1/subdir2/subdir3/anifsfile.sfx', '/', 1, | |
regexp_count('/usr/scottf/subdir1/subdir2/subdir3/anifsfile.sfx', '/'))+1); | |
-- | |
-- Returns: | |
-- anifsfile.sfx | |
-- | |
stop; | |
-- | |
-- Encapsulate the logic within a Scalar UDF | |
-- | |
create or replace function systools.Extract_IFS_filename ( | |
p_pathname dbclob(16m) ccsid 1200 | |
) | |
returns dbclob(10000) ccsid 1200 | |
deterministic | |
no external action | |
reads sql data | |
not fenced | |
set option COMMIT = *NONE | |
begin | |
return substr(p_pathname, regexp_instr(p_pathname, '/', 1, regexp_count(p_pathname, '/')) + 1); | |
end; | |
stop; | |
values systools.Extract_IFS_filename ('/usr/scottf/subdir1/subdir2/subdir3/anifsfile.sfx'); | |
-- | |
-- Returns: | |
-- anifsfile.sfx |
With SQL, its not uncommon to have multiple solutions to choose from.
Perhaps in our next Db2 for i Ping/Pong match, we can each have a turn explaining why we prefer our approach.
When is our next Db2 for i Ping/Pong? CEC? POW3R?
An excellent question indeed!
I only wish it were in person and soon.
If the goal is brevity, I usually prefer regexp_replace()
which is somewhat equivalent to how I used sed
in bash. It works by selecting the longest string at the end of the value which does not contain a slash. It also handles a filename with no path correctly.
values regexp_replace('/usr/scottf/subdir1/subdir2/subdir3/anifsfile.sfx','.*?([^/]+)$','\1');
Thanks for sharing your technique.
Since we're at it, why not give added value?
with pth as (
select '/usr/scottf/subdir1/subdir2/subdir3/anifsfile.sfx'
as path from sysibm.sysdummy1)
select
substr(path, 1, regexp_instr(path,'/', 1, regexp_count(path,'/')))
as folder,
substr(path, regexp_instr(path,'/', 1, regexp_count(path,'/'))+1)
as file
from pth
Great example, for using the REGEXP functions!
But for determining the IFS File Name there is IMHO an easier way, just by using the Locate_In_String Function:
Values(Substr('/usr/scottf/subdir1/subdir2/subdir3/anifsfile.sfx',
Locate_In_String('/usr/scottf/subdir1/subdir2/subdir3/anifsfile.sfx', '/', -1) + 1));