Re: print records that match regexp

On Jan 28, 6:11 pm, chas.ow...@xxxxxxxxx (Chas. Owens) wrote:
On Jan 28, 2008 4:12 PM, <bbrech...@xxxxxxxxx> wrote:

I have a table "customer_table" with the following fields:

Id int,
firstname varchar(64),
lastname varchar(64),
emailaddress varchar(64) not null primary key
city varchar (32),

Can some one help me and show me how to print only records that
matches a given regexp using, for example if I run:

#> getRecord.pl A+

should return all record from the database if the first name, last
name, or email address starts with a capital A

OR:

#> getRecord.pl

should return all records from the table, which I have it this way and
it works just fine:

snip

This really isn't a job for a regex. It is a job for a where clause.
Also, if the user where to pass A+ it would match any record whose
fields had one or more contiguous "A"s, not records that have fields
that start with "A". That match would be ^A. This further points to
the fact that you want the SQL operator LIKE (which does behave the
way you expect it to, but uses % instead of +).

snip> my $sth = $dbh->prepare("SELECT * FROM $tableName");

snip

my $arg = shift;
my $where_clause = "";
if ($arg) {
#handle meta characters, match uses % for one or more characters,
* for 0 or more, _ for any character
$arg =~ s/\+/%/g;
$arg =~ s/\?/_/g;
#try to handle SQL injection attacks, users should not be able to
break out of the string
#FIXME: this may not be a complete solution, it also probably
breaks character classes like [']
$arg =~ s/'/\\'/;
$where_clause = "where firstname like $arg or lastname like $arg
or email like $arg";

}

my $sth = $dbh->prepare("SELECT * from $tableName $where_clause");

By the way, comments are there to explain why you are doing something,
not to tell us what you are doing. Comments like "#print the data"
are useless. I can see that you are printing the data, why are you
printing it now?, what are you trying to achieve by printing it?



Thanks for your replay and instructions, as well as the tips regarding
the comments.
Sorry, I missed the "^" for the regexp ^A+
I applied your method but the query does not return any record from
the table.

Also when I try to match only one field using like:
my $arg = shift;
my $sth = $dbh->prepare (" SELECT * FROM $tableName firstname like
'$arg' ");
$sth->execute();

while(my $ref = $sth->fetchrow_hashref()) {
print "First Name: $ref->{'firstname'}\n";
print "Last Name: $ref->{'lastname'}\n";
}

if ($sth->rows == 0){
print "\n";
print "No record matched in the table \"$tableName\".\n";
print "\n";
}

query does not return any record, even I have some firstname staring
with "A", Albert, Aida in the table..

Thanks again for your help

.

Relevant Pages

  • Re: Efficient String Lookup?
    ... regexp language allowed embedded Perl code, ... The pattern is ... So the regexp engine tries the next option, ... I could put it inside a * to match all characters, ...
    (comp.lang.python)
  • Re: Regexp: m and [^[:alnum:]_] are not equivalent
    ... Sanitizing an arbitrary user input for regexp is not simple at all. ... - Find a candidate string with the standard search capabilities of the ... >> an explicit set of characters before. ... > boundaries". ...
    (comp.lang.tcl)
  • Re: Javascript: string detection
    ... to represent the one between LHS & RHS, and the first @ in the RegExp ensures that the second @ in the RegExp finds the first @ in the string. ... an attempted E-address, or a difficult maximal test to assure full compliance with one of the forms permitted by RFCs --- and that anything in between smacks of falling between two stools. ... Whilst I would hope that no-one in their right mind would ever want to reject those characters, and though I don't think narrowing down the precise set of allowable characters is sensible either, something as simple as limiting the presence of the at symbol and dot doesn't quite deserve to be labelled a half-hearted attempt. ...
    (comp.lang.javascript)
  • Re: Use of Like to extract data
    ... Dim RegExp As Object ... That will be the most common set of characters that I am working ... by a combination of a single alpha character followed by a space. ...
    (microsoft.public.excel.programming)
  • Re: Use of Like to extract data
    ... Dim RegExp As Object ... Set RegExp = CreateObject ... I look for a decimal preceeded by from 1 to 3 numeric characters. ...
    (microsoft.public.excel.programming)