Map file-glob-style patterns to SQL "LIKE" expressions, with escaping

More people seem to understand * for "0 or more characters" and ? for "1 single character" than the equivalents in SQL's "LIKE" patterns (percent and underscore!). This snippet maps a glob pattern (including backslash for escaping) to an SQL LIKE pattern.

BEGIN {
  my %mapping = split ' ', <<'END_OF_MAPS';
      \* *
      \? ?
      \\ \\
      \  \\
      %  \%
      _  \_
      *  %
      ?  _
END_OF_MAPS

  my $mapping_match = join "|", reverse sort map quotemeta($_), keys %
+mapping;
  $mapping_match = qr/$mapping_match/; # compile once

  sub pattern_to_like_string {
    my $pattern = shift;      # pattern made of * ? \ and non-specials

    $pattern =~ s/($mapping_match)/$mapping{$1}/g;

    return $pattern;
  }

}
[download]

Comment on Map file-glob-style patterns to SQL "LIKE" expressions, with escaping Download Code

Replies are listed 'Best First'.
Re: Map file-glob-style patterns to SQL "LIKE" expressions, with escaping by tlm (Prior) on Mar 31, 2005 at 22:57 UTC
Way cool, but why the `sort`, and more importantly why the `reverse` ??? the lowliest monk	[reply] [d/l] [select]
Re^2: Map file-glob-style patterns to SQL "LIKE" expressions, with escaping by merlyn (Sage) on Mar 31, 2005 at 23:10 UTC
"It's a trick!" It's to get longer things to appear before shorter things, so that the "\\" match is preferred over the "\" match, since it'll be first in the list (after a reversed sort). A simple sort-by-decreasing-length would have worked as well, but it takes more to type. :) -- Randal L. Schwartz, Perl hacker Be sure to read my standard disclaimer if this is a reply.	[reply]
Re: Map file-glob-style patterns to SQL "LIKE" expressions, with escaping by metaperl (Curate) on Apr 01, 2005 at 03:07 UTC
I'm confused on this. It looks like you are putting in characters for wildcard which won't be accepted by, for instance, MySQL, which only has `%` and `_` as wildcards, per the docs	[reply] [d/l] [select]
Re^2: Map file-glob-style patterns to SQL "LIKE" expressions, with escaping by graff (Chancellor) on Apr 01, 2005 at 03:21 UTC
If the original file-glob string is something like: `what\?_not\.%` [download] a file-glob sees a backslash escape before the "?" and "" (to match a literal question mark and asterisk), and treats "_" and "%" as normal (literal) characters. For conversion to an sql "like" pattern, the "\" must be removed from in front of "?" and "*" (because these characters don't need to be escaped now) and must be added in front of each "_" and "%" (to keep them literal, as they were in the file-glob).	[reply] [d/l]
Re^3: Map file-glob-style patterns to SQL "LIKE" expressions, with escaping by merlyn (Sage) on Apr 01, 2005 at 03:28 UTC
Precisely. This exact mapping took me enough time to work out that I thought I'd share it. You got it perfectly. -- Randal L. Schwartz, Perl hacker Be sure to read my standard disclaimer if this is a reply.	[reply]
Re^2: Map file-glob-style patterns to SQL "LIKE" expressions, with escaping by merlyn (Sage) on Apr 01, 2005 at 03:11 UTC
It maps * to % and ? to _, so I can write "??a*", and get any three-or-more character value where the 3rd character is "a". It's mapping to SQL. I'm not putting anything in there that isn't understood in standard SQL92. -- Randal L. Schwartz, Perl hacker Be sure to read my standard disclaimer if this is a reply.	[reply]