
There is real power in combing Vertica functions to accomplish almost any task in SQL.
Example:
I want to extract the last sub-directory from the STORAGE_PATH column in the DISK_STORAGE system table. Below are 2 methods for doing that combining several Vertica built-in functions.
dbadmin=> SELECT node_name,
dbadmin-> storage_path,
dbadmin-> split_part(storage_path, '/', regexp_count(storage_path, '/')+1) last_subdir
dbadmin-> FROM disk_storage;
node_name | storage_path | last_subdir
------------------+------------------------------------------------------+-----------------------
v_vmart_node0001 | /home/dbadmin/vmart/v_vmart_node0001_catalog/Catalog | Catalog
v_vmart_node0001 | /home/dbadmin/vmart/v_vmart_node0001_data | v_vmart_node0001_data
v_vmart_node0001 | /home/dbadmin/usershr | usershr
v_vmart_node0002 | /home/dbadmin/vmart/v_vmart_node0002_catalog/Catalog | Catalog
v_vmart_node0002 | /home/dbadmin/vmart/v_vmart_node0002_data | v_vmart_node0002_data
v_vmart_node0002 | /home/dbadmin/usershr | usershr
v_vmart_node0003 | /home/dbadmin/vmart/v_vmart_node0003_catalog/Catalog | Catalog
v_vmart_node0003 | /home/dbadmin/vmart/v_vmart_node0003_data | v_vmart_node0003_data
v_vmart_node0003 | /home/dbadmin/usershr | usershr
(9 rows)
vmart@sandbox1=> SELECT node_name,
vmart@sandbox1-> storage_path,
vmart@sandbox1-> SUBSTR(storage_path, INSTR(storage_path, '/', -1)+1) last_subdir
vmart@sandbox1-> FROM disk_storage;
node_name | storage_path | last_subdir
-----------------+------------------------------------------------------+-----------------------
v_vmart_node0001 | /home/dbadmin/vmart/v_vmart_node0001_catalog/Catalog | Catalog
v_vmart_node0001 | /home/dbadmin/vmart/v_vmart_node0001_data | v_vmart_node0001_data
v_vmart_node0001 | /home/dbadmin/usershr | usershr
v_vmart_node0002 | /home/dbadmin/vmart/v_vmart_node0002_catalog/Catalog | Catalog
v_vmart_node0002 | /home/dbadmin/vmart/v_vmart_node0002_data | v_vmart_node0002_data
v_vmart_node0002 | /home/dbadmin/usershr | usershr
v_vmart_node0003 | /home/dbadmin/vmart/v_vmart_node0003_catalog/Catalog | Catalog
v_vmart_node0003 | /home/dbadmin/vmart/v_vmart_node0003_data | v_vmart_node0003_data
v_vmart_node0003 | /home/dbadmin/usershr | usershr
(9 rows)
Helpful Links:https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/String/StringFunctions.htm
https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/RegularExpressions/RegularExpressionFunctions.htm
Have fun!