SELECT * FROM Songs WHERE Songs.Artist LIKE ? OR Songs.Artist LIKE '?
[download]

s/%/%%/ for $artist1, $artist2;
$dbh->execute("%$artist1%", "%$artist2%");
[download]

(I hope I remember correctly that % is escaped as %% in LIKE queries).

(I hope I remember correctly that % is escaped as %% in LIKE queries).

Um, nope. Perhaps you're confusing SQL LIKE with (s)printf?

Oracle, PostgreSQL, MySQL, SQLite, and even MS SQL Server have an ESCAPE keyword for this purpose. I'm too lazy today to find out if it is an official SQL standard or just one implementation copying the other one. But five very different databases agreeing on the same syntax for the same feature is simply too good to be just luck.

Alexander

--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

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]

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.

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?

$ 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]

Maybe this is what you are after:

my $sth = $dbh->prepare(q{SELECT * FROM Songs WHERE Songs.Artist LIKE 
+'%'+?+'%' OR Songs.Artist LIKE '%'+?+'%'
});
...
$sth->execute($one,$two);
[download]

I like that and didn't realize you could do it. Do you know if it's portable? Pg, mysql, sqlite, etc?

For postgres, the string concatenator would have to be '||' instead of '+'.

(A quick test seems to indicate SQLite wants '||' as well, which makes sense as that is the standard SQL concatenator)

see (maybe) also: dbi, placeholder and postgres regular expression problem

hth

Ok, thanks.

If I combine the suggestions from Jenda and erix it seems to work pretty well.

my @vals =("artist1", "artist2");
my $sth = $db->prepare(q{SELECT * FROM Songs WHERE Songs.Artist LIKE '
+%'||?||'%' OR Songs.Artist LIKE '%'||?||'%'});# suggestion from Jenda
my $results = $sth->execute( @vals ) or die("Failed to Execute SQL");
[download]

I'm now struggling to work out the syntax to combine the placeholder '%'||?\\'?' with a join and map statement that I can pass a an array of values.

Does anyone know the trick to get this syntax right?

If it matters - I am running ActivePerl on Vista X64 and using SQLite for my DB back end.

Each placeholder can accept only one value so you have to build your SQL statement with the right number of question marks. Or (if your database supports table valued functions) use something like this to split a single string on the database side. (I doubt things like this are supported by SQLite, but I may be mistaken.)

Jenda
Enoch was right!
Enjoy the last years of Rome.