The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Combining Functions to Extract Discrete Data From a String

Jim_KnicelyJim_Knicely Administrator
edited February 7 in Tips from the Team

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)

[email protected]=> SELECT node_name,
[email protected]>        storage_path,
[email protected]>        SUBSTR(storage_path, INSTR(storage_path, '/', -1)+1) last_subdir
[email protected]>   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

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/RegularExpressions/RegularExpressionFunctions.htm

Have fun!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.