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!
How to read first element from an arraylist which is stored as varchar

['Baby Unisex', 'Baby']
['Pre-Owned']
['Mens']
['Boys', 'Mens']
['Accessories', 'Bags']
['Clothing', 'Unisex']
['Boys', 'Baby Unisex']
['Girls', 'Bags']
['Accessories', 'Mens']
['Clothing']
['Shoes']
Tagged:
0
Best Answers
-
mosheg Vertica Employee Administrator
Another example on v11.0.2
CREATE TABLE orders( orderid INT, shipments ARRAY[VARCHAR(100)]); SET ESCAPE_STRING_WARNING TO OFF; SET STANDARD_CONFORMING_STRINGS TO OFF; COPY orders FROM STDIN DELIMITER '#' NULL '' COLLECTIONENCLOSE '\'' ABORT ON ERROR; 1#['Baby Unisex', 'Baby'] 2#['Pre-Owned'] 3#['Mens'] 4#['Boys', 'Mens'] 5#['Accessories', 'Bags'] 6#['Clothing', 'Unisex'] 7#['Boys', 'Baby Unisex'] 8#['Girls', 'Bags'] 9#['Accessories', 'Mens'] 10#['Clothing'] 11#['Shoes'] \. SELECT VERSION(); VERSION ------------------------------------- Vertica Analytic Database v11.0.2-0 (1 row) SELECT * FROM orders; orderid | shipments ---------+------------------------ 1 | ["Baby Unisex","Baby"] 2 | ["Pre-Owned"] 3 | ["Mens"] 4 | ["Boys","Mens"] 5 | ["Accessories","Bags"] 6 | ["Clothing","Unisex"] 7 | ["Boys","Baby Unisex"] 8 | ["Girls","Bags"] 9 | ["Accessories","Mens"] 10 | ["Clothing"] 11 | ["Shoes"] (11 rows) SELECT shipments[0] FROM orders WHERE orderid= 5; shipments ------------- Accessories (1 row) SELECT shipments[1] FROM orders WHERE orderid= 5; shipments ----------- Bags (1 row)
1 -
Jim_Knicely - Select Field - Administrator
@karthigaimuthu - The STRING_TO_ARRAY function was introduced in Vertica 9.3.1. But in Vertica 9.3.x you cannot define a Vertica managed table's column as an ARRAY, only an external table's column's data type. But starting in Vertica 10.0, you can define a managed table's column as an ARRAY!
0
Answers
Does this server your purpose?
Does this server your purpose?
Thanks @marcothesane for your quick turnaround. @mosheg has replied with desired result.
Thanks @mosheg it really helpful , but I unable to use it because we are using lower version of vertica we might have to upgrade it.
@mosheg , this function will work with vertica version v9.3.1-3/v10.0.1-2