vertica

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;