shorten the value by underscore in Vertica

We need to remove the 3rd segment (e0025895) and 7th segment (xxxx) to shorten the value in Vaertica. Please refer the sample data input and required output.

Value:

source_data_e0025895_pw2_px_77892_xxxx

east_match_a569820_kw2_lk_8975_p2585

Shorten Value:

source_data_pw2_px_77892

east_match_kw2_lk_8975

Solution

It's working

select 
 split_part(value, '_', 1) || '_' ||
 split_part(value, '_', 2) || '_' ||
 split_part(value, '_', 4) || '_' ||
 split_part(value, '_', 5) || '_' ||
 split_part(value, '_', 6) as Shorten_value
from mytable;