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