The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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



  • 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;`

  • 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:

  • 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