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

Best Answer

Answers

  • Jim_KnicelyJim_Knicely 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)
    
    

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.