SQL::AnchoredWildcards - add anchors ('^', '$') to SQL wildcards

  use SQL::AnchoredWildcards;
  $pattern = sql_anchor_wildcards($pattern);

SQL::AnchorWildcards enhances the default SQL SELECT..LIKE wildcard processing by adding support for the '^' and '$' anchor metacharacters. When using sql_anchor_wildcards(), if the pattern does not contain '^' ('$'), the search pattern is unanchored at the beginning (end). Escaping of '^' and '$' is done with '\'.

Please also note that '$' is properly escaped for Perl's benefit.

Let's take an SQL SELECT...LIKE Perl statement as in:

  my $query = new CGI;
  $query->import_names('Q');
  my $SQL =<<endSQL;
    SELECT
     GROUPNAME,
     GROUPID
    FROM
     TSR_GROUPS
    WHERE
     GROUPNAME LIKE '$Q::search';
  endSQL

which will yield the SQL statement:

    SELECT
     GROUPNAME,
     GROUPID
    FROM
     TSR_GROUPS
    WHERE
     GROUPNAME LIKE 'HDTV';

when the user has typed 'HDTV' into the text box named 'search' on the CGI input form. If 'HDTV' is the search term, then SQL SELECT..LIKE will only find groups whose name matches /^HDTV$/. If the CGI uses:

  $Q::search = sql_anchor_wildcards($Q::search);

the SQL statement becomes:

    SELECT
     GROUPNAME,
     GROUPID
    FROM
     TSR_GROUPS
    WHERE
     GROUPNAME LIKE '%HDTV%';

which will find all groups that have 'HDTV' as part of the group's name. I think that most people that use a Web-based search engine would expect that typing 'HDTV' into a search box would make the search engine find all groups that have 'HDTV' as part of the group's name.

But what if you only want to find groups whose name starts with 'HDTV' -- say, all HDTV engineering groups like 'HDTV Power Supply Design' where other, non-engineering groups would have 'HDTV' later in their names, like 'Marketing & Sales -- HDTV Direct View'? In that case with SQL::AnchoredWildcards, you would type '^HDTV' into the CGI search box, with:

    SELECT
     GROUPNAME,
     GROUPID
    FROM
     TSR_GROUPS
    WHERE
     GROUPNAME LIKE 'HDTV%';

as the resulting SQL. As you would expect, this SQL SELECT..LIKE statement would find groups whose name starts with 'HDTV'.

Similarly, if you know that the groups you want are those that have 'HDTV' as the last component of their names, like 'Marketing & Sales -- Direct View HDTV', you would type 'HDTV$' into the CGI search box, with:

    SELECT
     GROUPNAME,
     GROUPID
    FROM
     TSR_GROUPS
    WHERE
     GROUPNAME LIKE '%HDTV';

as the resulting SQL.

If you are looking for an embedded '$', as in:

    DCL LOGICAL $SYS_INFO

you would type 'DCL LOGICAL \$SYS_INFO' into the CGI search box, with:

    SELECT
     GROUPNAME,
     GROUPID
    FROM
     TSR_GROUPS
    WHERE
     GROUPNAME LIKE 'DCL LOGICAL $SYS_INFO';

as the resulting SQL.

Mark Leighton Fisher, fisherm@tce.com

perl(1).