Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

DBI placeholders and like statement

by jfroebe (Parson)
on Jun 18, 2013 at 16:52 UTC ( #1039600=perlquestion: print w/replies, xml ) Need Help??
jfroebe has asked for the wisdom of the Perl Monks concerning the following question:

AFAIK, you can't use a placeholder with a SQL like statement. A coworker says you can. Can anyone confirm or deny it?

$query = 'select name from my_table where name like ?%'; my $sth = $dbh->prepare($query); $sth->execute('Jo');

Basically he is convinced that you can. Nothing I've shown him has convinced him otherwise. Hopefully some prince or princess of the Perl Monks can break this stalemate


poj pointed out that I was incorrect. The percent sign belongs in the execute() not the prepare query.

Jason L. Froebe

Blog, Tech Blog

Replies are listed 'Best First'.
Re: DBI placeholders and like statement
by poj (Monsignor) on Jun 18, 2013 at 17:00 UTC
    This works on MySQL ;
    $query = 'select name from my_table where name like ?'; my $sth = $dbh->prepare($query); $sth->execute('Jo%');

      Don't I have egg on my face! :) I was putting the percent sign in the query itself.

      Thanks again!

      Jason L. Froebe

      Blog, Tech Blog

        Well, you could say:
        where name like ? || '%'
        But that still won't use an index on the column since the query optimizer doesn't know where any other wildcard will be, whereas hardcoding the entire argument might use an index:
        where name like 'abc%'

        Don't worry about it. First time I tried using like with placeholders I did the same thing. :)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1039600]
Front-paged by Corion
and the daffodils sway...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (11)
As of 2018-06-22 18:40 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (124 votes). Check out past polls.