Convert Comma Separated Values To Each Separate Row with v_txtindex.StringTokenizerDelim

davdsdavds Vertica Customer
edited December 2022 in General Discussion

our current vertica version: Vertica Analytic Database v9.2.1-28

I tried to use v_txtindex.StringTokenizerDelim function to parse list to row.

--this works
select v_txtindex.StringTokenizerDelim('1,2,3,4,5,19 ',',') over () from dual

--this not works

select list_value,v_txtindex.StringTokenizerDelim(list_value,',') over ()
from tmp_list_val
where id = '12345';

And got error: ERROR: Function v_txtindex.StringTokenizerDelim(long varchar, unknown) does not exist, or permission is denied for v_txtindex.StringTokenizerDelim(long varchar, unknown)

please advice



  • Options
    marcothesanemarcothesane - Select Field - Administrator

    this means that list_value in your example must be a LONG VARCHAR

    SELECT list_value, v_txtindex.StringTokenizerDelim(list_value::VARCHAR(65000) OVER(PARTITION BEST) FROM tmp_ist_val;`

  • Options
    Ariel_CaryAriel_Cary Vertica Employee Employee

    As of Vertica v12.0.2, v_txtindex.StringTokenizerDelim supports long varchar arguments. Meanwhile, you could use the varchar workaround if you don't mind truncating your data.
    There was a similar discussion here:

  • Options
    davdsdavds Vertica Customer

    thanks, Ariel and marcothesane!!

    The issue got resolve now!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file