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:

Best Answers

  • moshegmosheg Employee
    Accepted Answer

    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)
    
    
  • Jim_KnicelyJim_Knicely Administrator
    Accepted Answer

    @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!

Answers

  • Does this server your purpose?

    WITH indata(s) AS (
              SELECT '[''Baby Unisex'', ''Baby'']'
    UNION ALL SELECT '[''Pre-Owned'']'
    UNION ALL SELECT '[''Mens'']'
    UNION ALL SELECT '[''Boys'', ''Mens'']'
    UNION ALL SELECT '[''Accessories'', ''Bags'']'
    UNION ALL SELECT '[''Clothing'', ''Unisex'']'
    UNION ALL SELECT '[''Boys'', ''Baby Unisex'']'
    UNION ALL SELECT '[''Girls'', ''Bags'']'
    UNION ALL SELECT '[''Accessories'', ''Mens'']'
    UNION ALL SELECT '[''Clothing'']'
    UNION ALL SELECT '[''Shoes'']'
    )
    SELECT
      s
    , (STRING_TO_ARRAY(s))[0] AS firstelement
    FROM indata;
    -- out             s            | firstelement  
    -- out -------------------------+---------------
    -- out  ['Baby Unisex', 'Baby'] | 'Baby Unisex'
    -- out  ['Pre-Owned']           | 'Pre-Owned'
    -- out  ['Mens']                | 'Mens'
    -- out  ['Boys', 'Mens']        | 'Boys'
    -- out  ['Accessories', 'Bags'] | 'Accessories'
    -- out  ['Clothing', 'Unisex']  | 'Clothing'
    -- out  ['Boys', 'Baby Unisex'] | 'Boys'
    -- out  ['Girls', 'Bags']       | 'Girls'
    -- out  ['Accessories', 'Mens'] | 'Accessories'
    -- out  ['Clothing']            | 'Clothing'
    -- out  ['Shoes']               | 'Shoes'                                                                                                    
    
  • Does this server your purpose?

    WITH indata(s) AS (
              SELECT '[''Baby Unisex'', ''Baby'']'
    UNION ALL SELECT '[''Pre-Owned'']'
    UNION ALL SELECT '[''Mens'']'
    UNION ALL SELECT '[''Boys'', ''Mens'']'
    UNION ALL SELECT '[''Accessories'', ''Bags'']'
    UNION ALL SELECT '[''Clothing'', ''Unisex'']'
    UNION ALL SELECT '[''Boys'', ''Baby Unisex'']'
    UNION ALL SELECT '[''Girls'', ''Bags'']'
    UNION ALL SELECT '[''Accessories'', ''Mens'']'
    UNION ALL SELECT '[''Clothing'']'
    UNION ALL SELECT '[''Shoes'']'
    )
    SELECT
      s
    , (STRING_TO_ARRAY(s))[0] AS firstelement
    FROM indata;
    -- out             s            | firstelement  
    -- out -------------------------+---------------
    -- out  ['Baby Unisex', 'Baby'] | 'Baby Unisex'
    -- out  ['Pre-Owned']           | 'Pre-Owned'
    -- out  ['Mens']                | 'Mens'
    -- out  ['Boys', 'Mens']        | 'Boys'
    -- out  ['Accessories', 'Bags'] | 'Accessories'
    -- out  ['Clothing', 'Unisex']  | 'Clothing'
    -- out  ['Boys', 'Baby Unisex'] | 'Boys'
    -- out  ['Girls', 'Bags']       | 'Girls'
    -- out  ['Accessories', 'Mens'] | 'Accessories'
    -- out  ['Clothing']            | 'Clothing'
    -- out  ['Shoes']               | 'Shoes'                                                                                                    
    
  • 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

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.