Re: print records that match regexp

On Jan 28, 2008 4:12 PM, <bbrecht56@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?
.

Relevant Pages

  • Re: read(5,*) problem?
    ... whereas the carriage-control characters were interpreted by the ... EBCDIC has CR, LF, and NL characters. ... specify the operation that will occur after printing the line. ...
    (comp.lang.fortran)
  • Re: A note on computing thugs and coding bums
    ... Richard Heathfield, I doubt I would be able to keep my head as he has ... It would handle international characters if the execution character ...
    (comp.programming)
  • Re: [Gum] YASD
    ... R. Dan Henry wrote: ... unbalanced stats. ... early Zangband's power-up and plain rolled characters will tend to be ...
    (rec.games.roguelike.angband)
  • Re: FMA - Episode 29
    ... Don Giovanni refusing the order to repent, ... >> the knowledge of the characters and the knowledge of the audience. ... Regardless of whether Ed recognizes the natural images ...
    (rec.arts.anime.misc)
  • Re: comments on cipher please
    ... this makes sure that each key generated is 8 characters long and not ... character key that is used to cipher the file. ... PassKeyRight = CryptedFileData xor PassKeyRight ...
    (sci.crypt)