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';
Mark Leighton Fisher, fisherm@tce.com
perl(1).
To install SQL::AnchoredWildcards, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQL::AnchoredWildcards
CPAN shell
perl -MCPAN -e shell install SQL::AnchoredWildcards
For more information on module installation, please visit the detailed CPAN module installation guide.