Re^6: wild cards in Vertica

[pragov]

I need to see WHERE m.processed_time BETWEEN to_timestamp('20150630003500','YYYYMMDDHH24MISS') AND to_timestamp('20150630020559', 'YYYYMMDDHH24MISS')AND CLName LIKE('A%','MET-CL1',BDO-CL1')

[GotToBTru]

If you typed that in, would your database accept it? That doesn't look valid to me. I would expect something more like:

WHERE ... AND ((CLName LIKE 'A%') OR (CLName in ('MET-CL1','BDO-CL1'))

The following will divide the provided values into two arrays, @pre to hold prefixes, @words to hold complete values. It assumes one letter by itself is a prefix. Anything else is a complete value.

if ($clnm) {
  @parts = split /,/,$clnm;
  map { m/\'\w\'/ ? push @pre, $_ : push @words, $_ } @parts;

Functions like join can be used to format the LIKE clause and the IN clause. Here's one way to construct the IN clause:

 $in_clause = sprintf "( CLName IN (%s) )", join ',',@words;

Then you will need some logic to figure out what connecting terms (AND, OR) you will need.

[marinersk]

If I'm reading you correctly:

AND CLName LIKE('A%','MET-CL1',BDO-CL1')

Should become:

AND (      CLName LIKE 'A%'
        OR CLName LIKE '%MET-CL1%'
        OR CLName LIKE '%BDO-CL1%'
    )

[erix]

AND (      CLName LIKE 'A%'
        OR CLName LIKE '%MET-CL1%'
        OR CLName LIKE '%BDO-CL1%'
    )

As the OP is working a postgres derivative: in postgres (like perl, a bit TIMTOWTDI) that could be written:

AND CLName LIKE ANY (array['A%','%MET-CL1%','%BDO-CL1%'])

[pragov]

Yes That is the exact way I need the SQL WHERE as It's not always A.It could be any prefix and followed by complete CLNAME. AND CLName LIKE('A%','MET-CL1',BDO-CL1'). But If there is no prefix, then it should work for AND CLName LIKE('ATN-CL1','MET-CL1',BDO-CL1') as well. Thanks