Re^4: wild cards in Vertica

Yes..the prefix is always like A or B or M etc. I tried this way


if ($clnm  ne ""){
        $cnt = @parts = split(/\,/, $clnm );
       # $clnm  = "'";
        $WHERE = $WHERE ." AND (";
        for ($i = 0; $i < $cnt; $i++) {
             $WHERE = $WHERE . "m.CLNAME LIKE '$parts[$i]%' ";
             ($WHERE = $WHERE .  " OR ") if ($i < $cnt - 1);
        }
       $WHERE = $WHERE . ")";

}
[download]

but still did not work. getting output as: AND m.CLNAME IN (A,BDO-CL1) Should I modify here too?

if ($clnm ne "") { $Where = $Where . "AND m.CLNAME IN ($clnm) "; }
[download]

Thanks,

Comment on Re^4: wild cards in Vertica Select or Download Code

Replies are listed 'Best First'.
Re^5: wild cards in Vertica by GotToBTru (Prior) on Jun 30, 2015 at 19:25 UTC
Saying it doesn't work doesn't help. For the THIRD time, please answer my very simple question .. what value do you want to see in $Where? Dum Spiro Spero	[reply]
Re^6: wild cards in Vertica by pragov (Novice) on Jun 30, 2015 at 19:42 UTC
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')	[reply]
Re^7: wild cards in Vertica by GotToBTru (Prior) on Jun 30, 2015 at 22:03 UTC
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'))` [download] 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;` [download] 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;` [download] Then you will need some logic to figure out what connecting terms (AND, OR) you will need. Dum Spiro Spero	[reply] [d/l] [select]
Re^7: wild cards in Vertica by marinersk (Priest) on Jul 01, 2015 at 04:07 UTC
If I'm reading you correctly: `AND CLName LIKE('A%','MET-CL1',BDO-CL1')` [download] Should become: `AND ( CLName LIKE 'A%' OR CLName LIKE '%MET-CL1%' OR CLName LIKE '%BDO-CL1%' )` [download]	[reply] [d/l] [select]
Re^8: wild cards in Vertica by erix (Prior) on Jul 02, 2015 at 14:12 UTC
Re^8: wild cards in Vertica by pragov (Novice) on Jul 06, 2015 at 15:29 UTC