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 Vertica Employee Administrator
    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 - Select Field - Administrator
    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

  • marcothesanemarcothesane - Select Field - Administrator

    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'                                                                                                    
    
  • marcothesanemarcothesane - Select Field - Administrator

    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