How “LIKE” by LONG VARCHAR?

Question

I had DB Vertica 8.0. Create schema WAREHOUSE with table include field by type LONG VARCHAR. Now I tried to execude SELECT for example

SELECT * FROM WAREHOUSE.ALL_EVENTS a 
WHERE 
a.original_data like '%d963%'

returned error

SQL Error [4286] [42883]: [Vertica][VJDBC](4286) ERROR: Operator does not exist: long varchar ~~ unknown
  [Vertica][VJDBC](4286) ERROR: Operator does not exist: long varchar ~~ unknown
    com.vertica.util.ServerException: [Vertica][VJDBC](4286) ERROR: Operator does not exist: long varchar ~~ unknown

In Oracle I used dbms_lob package for CLOB fields.

Vertica have simular package for LONG VARCHAR types?

How "LIKE" by LONG VARCHAR?

Answer 1

As explained in the fine manual the (SQL standard) LIKE predicate in Vertica accept CHAR, VARCHAR, BINARY and VARBINARY data types.

To perform LIKE operations on LONG VARCHAR columns you can use REGEXP_LIKE (no need to install/use any special package). This way:

SELECT *
FROM WAREHOUSE.ALL_EVENTS a 
WHERE REGEXP_LIKE(a.original_data, 'd963');

That's all.