There may not be a fully portable answer to this. My best guess for a concise answer would be:     "... LIKE '%" . quotemeta($str) . "%' ..." but I fear that some database won't handle all of the potential backslashes. So a more conservative approach might be called for:

    my $pattern= $string_from_user;
    $pattern =~ s#(['%_\[\]])#\\$1#g;
    "... LIKE '%$pattern%' ..."
[download]

Do all databases even support \ for escaping LIKE "wildcards"? What about for escaping single quotes (apostrophes)? I believe that using two consecutive apostrophes to represent a single apostrophe when inside of an apostrophe-delimited string is part of the SQL standard. So 'don''t' = "don't" in SQL. I've long been curious what the standard says about LIKE strings but have always ended up looking up what my current database supports instead (which has always been the use of \ to escape things).

In dealing with MySQL for PerlMonks, I found what I consider to be a quirk. If I want to find all strings containing a backslash followed by a percent sign (\%), I have to tell MySQL     LIKE '%\\\\\%%' (which means I'd have to tell Perl "... LIKE '%\\\\\\\\\\%%' ...") because, when I use what I would have expected to work (LIKE '%\\\%%'), it appears that some backslahses are consumed when MySQL parses the single-quoted string and the LIKE operation ends up seeing %\\%%, which finds strings that contain just a backslash.

So you might have to resort to:

    # Escape "wildcard" characters in user input:
    my $pattern= quotemeta($string_from_user);
    # Escape things processed by 'single quote' parsing:
    $pattern =~ s#([\\'])#\\$1#g;
    "... LIKE '%$pattern%' ..."
[download]

Try something like this for your select and bind the user entered string to the parameter

---
print map { my ($m)=1<<hex($_)&11?' ':'';
$m.=substr('AHJPacehklnorstu',hex($_),1) }
split //,'2fde0abe76c36c914586c';
[download]

Aha! It works. But, will this have the performance problems associated with using place holders and saying $sth->execute->("foo%")? It mentioned here that it didn't use the Index like it should. I'm not a database expert, so I don't know how to test this.

Since these LIKE strings start with a '%', the database can't use an index at all. So that should not be a concern here.

        - tye

There's a discussion of binding and LIKE clauses in A DBI Query Binding Pitfall. The upshot is that RDBMS query optimizers will assume the worst if they see a LIKE clause but don't have a string to examine to see if index use is possible. There are workarounds.

This is sort of off topic but I think it should be said.

This works really good in Oracle 9i where you can index soundex for speed.

Here is a example.

    select * from demographics where soundex(name) = soundex(?)
[download]