Skip to main content
Question

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

  • December 7, 2022
  • 3 replies
  • 10 views

davds
Forum|alt.badge.img+1
  • Participating Frequently

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

thanks
David

3 replies

marcothesane
Forum|alt.badge.img+1
  • Participating Frequently
  • December 7, 2022

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

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


Ariel_Cary
Forum|alt.badge.img
  • Participating Frequently
  • December 7, 2022

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:
https://forum.vertica.com/discussion/comment/248204#Comment_248204


davds
Forum|alt.badge.img+1
  • Author
  • Participating Frequently
  • December 7, 2022

thanks, Ariel and marcothesane!!

The issue got resolve now!