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

rajatpaliwal86

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?

Jim_Knicely

This works too...

dbadmin=> SELECT REGEXP_LIKE(c[0], '[0-9]+'), REGEXP_LIKE(c[1], '[0-9]+'), REGEXP_LIKE(c[2], '[0-9]+') FROM a;
 REGEXP_LIKE | REGEXP_LIKE | REGEXP_LIKE
-------------+-------------+-------------
 f           | f           | t
(1 row)

Jim_Knicely

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)

rajatpaliwal86

@Jim_Knicely Thank you!!