wild cards in Vertica

pragov has asked for the wisdom of the Perl Monks concerning the following question:

Hi All,

I have a perl-Vertica question. I need to pass a combination of a wild card and complete value of a field. It will be a combination of prefix and others. The example is ('A','BCD-UT1','MDO-CT1'). The actual value of A is ABC-EF2.But users can just click on Prefix,A to get all values starting with A. I tried,

if ($clnm ne ""){
$cnt = @parts = split(/\,/, $ clnm );
$clnm = "'";
for ($i = 0; $i < $cnt; $i++) {
$clnm = $clnm . $parts[$i];
($clnm = $clnm . "%','") if ($i < $cnt - 1);
}
$clnm = $clnm . "%'";
}
and then used it in

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

It is not working. The SQL either takes LIKE('A%') or IN ('ABC-EF2','BCD-UT1','MDO-CT1'), but not the combination. How to retrieve the value,('A','BCD-UT1','MDO-CT1')?

Thanks, pragov

Comment on wild cards in Vertica Download Code

Replies are listed 'Best First'.
Re: wild cards in Vertica by KurtSchwind (Chaplain) on Jul 01, 2015 at 12:03 UTC
It looks like you have a space between $clnm and the %. In SQL "A %" will match A <space> anything. I think from you description you want "A%". Try `if ($clnm ne "") { $Where = $Where . qq(AND m.CLNAME LIKE '$clnm).qq(% +’ OR m.CLNAME IN ('$clnm') ); }.` [download] -- “For the Present is the point at which time touches eternity.” - CS Lewis	[reply] [d/l]
Re^2: wild cards in Vertica by pragov (Novice) on Jul 13, 2015 at 13:28 UTC
Hi, I have this function as `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] How do I use this to get your statement if ($clnm ne "") { $Where = $Where . qq(AND m.CLNAME LIKE '$clnm).qq(% +’ OR m.CLNAME IN ('$clnm') ); }? Thanks	[reply] [d/l]
Re: wild cards in Vertica by GotToBTru (Prior) on Jun 30, 2015 at 18:16 UTC
What is the SQL you want to end up with in $Where? Update: part of the issue may be your program cannot tell what is a prefix (and needs the % appended) and what is a complete value that should be matched verbatim. Dum Spiro Spero	[reply]
Re^2: wild cards in Vertica by pragov (Novice) on Jun 30, 2015 at 18:26 UTC
The SQL is as follows $sql = $select.$from.$Where; $from is the list of fields from the table. and $Where is the filters. How do I tweak my program, to tell what is a prefix and what is a complete value? Thanks, Pragov	[reply]
Re^3: wild cards in Vertica by GotToBTru (Prior) on Jun 30, 2015 at 18:32 UTC
What value do you want to be in $Where? As to how to tell what is prefix, I don't know enough about your data. Is a 1 letter entry always a prefix? You can always use LIKE and % but it will hurt the efficiency of your queries. That may not matter to you. Dum Spiro Spero	[reply]
Re^4: wild cards in Vertica by pragov (Novice) on Jun 30, 2015 at 19:20 UTC
Re^5: wild cards in Vertica by GotToBTru (Prior) on Jun 30, 2015 at 19:25 UTC
Some notes below your chosen depth have not been shown here
Re: wild cards in Vertica by chacham (Prior) on Jul 01, 2015 at 12:12 UTC
Dynamic SQL on its own it not secure. This adds confusion to the mix as well. Might i suggest you reconsider and use static SQL. Passing IN clauses with an known number of arguments is easy. It's the unknown that is hard, and each RDBMS has a different solution. Mostly, it requires a recursive CTE that will separate a passed string into its individual elements. You can search for the solution with stored procedures (for your RDBMS), which deal with a similar issue and have a plethora of answers.	[reply]