how to use regexes in SELECT statement

coldfingertips has asked for the wisdom of the Perl Monks concerning the following question:

My script errors out when I try to use a regex in the following statement. Can someone show me the proper way to go about doing this?

Also, I'm used to selecting a number of rows from a mysql database and running over it with sth->fetch and bind_column. Is there a way I can set a variable to a one-column select? Ie. my $name = qq(select name from db where id="1");

My current problem is

   my $data = qq(SELECT catname, path FROM categories WHERE path =~ m/
+^$path/ && path != "$path");
   my $sth = $dbh->prepare($data);
   $sth->execute() or die $dbh->errstr;
[download]

Comment on how to use regexes in SELECT statement Download Code

Replies are listed 'Best First'.
Re: how to use regexes in SELECT statement by agianni (Hermit) on Mar 30, 2007 at 02:52 UTC
As Herkum suggested, you can't simply use Perl regexes in SQL. But based on what you're trying to do, you probably don't need regexes anyway. You can use LIKE. Also, you're best off binding your params rather than including variables in your SQL: `my $data = qq( SELECT catname, path FROM categories WHERE path LIKE ? AND path != ? ); my $sth = $dbh->prepare($data); $sth->execute( "$path/%", $path ) or die $dbh->errstr;` [download]	[reply] [d/l]
Re: how to use regexes in SELECT statement by Herkum (Parson) on Mar 30, 2007 at 01:58 UTC
You are mixing Perl code into your Database SELECT statement. That is not going to work at all. If you want to use regular expression in your MySQL statement then you should look here.	[reply]
Re: how to use regexes in SELECT statement by davorg (Chancellor) on Mar 30, 2007 at 09:24 UTC
You don't say which database you are using. I've heard rumours of some database systems which allow Perl(ish) regular expressions in their SQL, but this isn't a standard SQL feature. -- See the Copyright notice on my home node. "The first rule of Perl club is you do not talk about Perl club." -- Chip Salzenberg	[reply]
Re: how to use regexes in SELECT statement by johngg (Canon) on Mar 30, 2007 at 09:18 UTC
Slightly OT in that this doesn't address the use of regexen inside a `SELECT`, regarding which you already have answers. You want to make sure a string starts with `$path` but the string must not be equal to `$path`, i.e. it must be longer. You can do that by changing the regex to force it to match a character after `$path` `$ perl -le ' > $path = q{/ab/cd/e}; > $string = q{/ab/cd/e}; > print $string, $string =~ m{^$path.} ? q{ - Match} : q{ - No}; > $string .= q{f}; > print $string, $string =~ m{^$path.} ? q{ - Match} : q{ - No};' /ab/cd/e - No /ab/cd/ef - Match $` [download] I hope this is of use. Cheers, JohnGG	[reply] [d/l] [select]