Can we do a regex search on vertica's ARRAY (complex type) data type?

https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/ARRAY.htm
Can we do a regex search on each element or possibly can we cast it to varchar and do regex?

Best Answer

Answers

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    You can EXPLODE the array and look at each element.

    Example:

    dbadmin=> CREATE TABLE a (c ARRAY[VARCHAR]);
    CREATE TABLE
    
    dbadmin=> INSERT INTO a SELECT ARRAY['CAT','DOG','PIG123'];
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> SELECT explode(c) over() FROM a;
     position | value
    ----------+--------
            0 | CAT
            1 | DOG
            2 | PIG123
    (3 rows)
    
    dbadmin=> SELECT c, REGEXP_LIKE(c, '[0-9]+') FROM (SELECT explode(c) over() as (pos, c) FROM a) foo;
       c    | REGEXP_LIKE
    --------+-------------
     CAT    | f
     DOG    | f
     PIG123 | t
    (3 rows)
    

    Or you can look at the entire ARRAY as a string:

    dbadmin=> SELECT REGEXP_LIKE(ARRAY_TO_JSON(c), '[0-9]+') FROM a;
     REGEXP_LIKE
    -------------
     t
    (1 row)
    
    
  • rajatpaliwal86rajatpaliwal86 Vertica Customer

    @Jim_Knicely Thank you!!

Leave a Comment

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