Combining Functions to Extract Discrete Data From a String
Jim_Knicely
- Select Field - Administrator
Vertica has 1000s of built-in functions including String Functions, which perform conversion, extraction, or manipulation operations on strings, or return information about strings and Regular Expression Functions, which 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/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/String/StringFunctions.htm
Have fun!
0