in reply to SQL LIKE and Binding Variables

Following up on what moritz said:

First (for mysql, at least) the way to include a literal "%" or literal "_" as part of the search condition in the "LIKE" value is to put a backslash in front of that character -- e.g.  % 1\_000\% % will match any string field value that contains a literal " 1_000% ", with anything (or nothing) before and after.

As a more general method for conjoining different "like" conditions (updated as per "###" comments): 

my @sources = ( 'artist 1', 'artist 2', 'whatever' );  ### we make an 
+array first...
my @values = map { s/([_%])/\\$1/g; "%$_%" } @sources;  ### ...to avoi
+d an error here
my $like_clause = join( " or ", map { "Songs.Artist LIKE ?" } @values;

my $sth = $dbh->prepare( "SELECT * FROM Songs where ($like_clause)" );
$sth->execute( @values );

[download]
(I like putting parens around a set of related "OR" conditions -- it's a handy safeguard in case I need to include some other condition using "AND".)

UPDATE: Sorry about having posted unusable code earlier -- I should have tested my initial version, to see that it produced the error mentioned in Mad_Mac's reply below. My code has been fixed now, as indicated in the comments above.

Replies are listed 'Best First'.
Re^2: SQL LIKE and Binding Variables
by Mad_Mac (Beadle) on Nov 03, 2009 at 22:23 UTC

    Thanks. I thought blackslash was the right way to escape "%" in a SQL statement also, but I couldn't seem to get the syntax right.

    I tried your recommendation, but all I managed to get was a different error: "Modification of a read-only value attempted at ..." and it gives me a reference to the line with the my @values = map { s/([_%])/\\$1/g; "%$_%" } bit. Maybe I didn't implement it quite right?

    Here's what my test code looks like at the moment:

    #!/usr/bin/perl
use warnings;
use DBI;
my $db = DBI->connect("dbi:SQLite:music.sqlite","","");
my @values = map { s/([_%])/\\$1/g; "%$_%" } ( 'pearl', 'temple');
my $like_clause = join( " or ", map { "Songs.Artist LIKE ?" } @values)
+;
my $sth = $db->prepare( "SELECT * FROM Songs where ($like_clause)" );
my $all = $sth->execute( @values ) or die("Failed to Execute SQL");

foreach my $row (@$all) {
my ($id, $Track, $Title, $Artist, $Album, $Year, $Genre, $Path) = @$ro
+w;
print "\nid: $id\nTrack: $Track\nTitle: $Title\nArtist: $Artist\nAlbum
+: $Album\nYear: $Year\nGenre: $Genre\nPath: $Path\n";

    [download]

    Any other thoughts?

[reply]
[d/l]
[select]
      $ perl -Mdiagnostics -e "s//1/g for 1"
Modification of a read-only value attempted at -e line 1 (#1)
    (F) You tried, directly or indirectly, to change the value of a
    constant.  You didn't, of course, try "2 = 1", because the compile
+r
    catches that.  But an easy way to do the same thing is:

        sub mod { $_[0] = 1 }
        mod(2);

    Another way is to assign to a substr() that's off the end of the s
+tring.

    Yet another way is to assign to a foreach loop VAR when VAR
    is aliased to a constant in the look LIST:

            $x = 1;
            foreach my $n ($x, 2) {
                $n *= 2; # modifies the $x, but fails on attempt to mo
+dify the 2
            }

Uncaught exception from user code:
        Modification of a read-only value attempted at -e line 1.
 at -e line 1

      [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom