Re: Trying to get LIKE to work

[Ronald J Kimball]

On Thu, Nov 14, 2002 at 08:44:48AM -0500, Kipp, James wrote:
> I have a simple query using a LIKE in the statement and I just can get it to
> return any data. I know it works because i can do it in sqlplus and get data
> returned. I have tried everything to try to quote it correctly, like qq, qw,
> dbh->quote and finally placeholders. Code is below, any ideas ?
> 
> Thanks 
> Jim
> ------
> 
> # my $string = $dbh->quote("%7-Aug%");
> my $date = qq(7-Aug);
> 
> my $sth = $dbh->prepare("SELECT * FROM uptime WHERE sdate LIKE ?" )
>     or die "can't prepare: $DBI::errstr\n";
> 
> $sth->execute("%$date%") or die "can't execute $DBI::errstr\n";
> my @row;
> while (@row = $sth->fetchrow_array() ) {
>         print "@row\n";
> }

Since you mention sqlplus, I assume you are using Oracle...  One
possibility is that the NLS_DATE_FORMAT (the default format for dates)
differs between your sqlplus and DBI sessions.

I would recommend using TO_CHAR to format the date in the WHERE clause:

my $date = '7-Aug';

my $sth = $dbh->prepare(<<"EndOfSQL");
  SELECT *
  FROM   update
  WHERE  TO_CHAR(sdate, 'DD-Mon') LIKE ?
EndOfSQL

$sth->execute("%$date%");


In fact, if you're just looking for Aug 7, rather than Aug 7, 17, & 27, you
can then use = instead of LIKE:

my $date = '07-Aug';

my $sth = $dbh->prepare(<<"EndOfSQL");
  SELECT *
  FROM   update
  WHERE  TO_CHAR(sdate, 'DD-Mon') = ?
EndOfSQL

$sth->execute($date);


I hope that's helpful!

Ronald