Combining Functions to Extract Discrete Data From a String: Quick Tip

Posted February 7, 2019 by Jim Knicely, Vertica Principal Solution Architect

Vertica has 1000s of built-in functions including String Functions, which perform conversion, extraction, or manipulation operations on strings or return information about strings. Regular Expression Functions let you perform pattern matching on strings of characters.

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!