/ 
SQL-AnchoredWildcards-1.0
River stage zero No dependents
/ SQL::AnchoredWildcards

NAME

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

SYNOPSIS

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

DESCRIPTION

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.

EXAMPLES

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.

AUTHOR

Mark Leighton Fisher, fisherm@tce.com

SEE ALSO

perl(1).