|
Re: Regex matching on anywhere but the beginning or end of a line
by xmath (Hermit) on Feb 23, 2003 at 00:03 UTC
|
You can use positive zero-width assertions:
my $t = q["string (12" or 1 foot or 1') into 6" MySQL varchar"];
$t =~ s/(?<=.)"(?=.)/""/g;
print "$t\n";
This means literally 'A double-quote that is preceded by a any char and followed by any char'. See perldoc perlre for the details.
•Update: corrected terminology
•Update: if the string can contain newlines, don't forget to add the /s modifier to the substitution | [reply]
[d/l]
[select] |
|
|
Thank you all for your replies. I spent a couple of hours fighting with this and have the perlre man page pretty much memorized. The answer was in searching for what is rather than looking for two instances of what is not. The best part is I found this site!
| [reply] |
Re: Regex matching on anywhere but the beginning or end of a line
by dws (Chancellor) on Feb 23, 2003 at 04:07 UTC
|
Let's take a step back and look at what you're trying to achieve. From the presence of "MySQL" in your example string, I'm wondering if you aren't trying to "quote" a string so that you can use it to construct an INSERT query for MySQL. If that's the case, you're better off leaving the undoubled quote in the string, using a query parameter in your SQL, and passing the unquoted string to execute(), which will correctly quote its arguments.
For example:
my $sth = $dbh->prepare("INSERT INTO foo VALUES(?)");
$sth->execute($stringThatMightOrMightNotContainQuotes);
| [reply]
[d/l]
[select] |
Re: Regex matching on anywhere but the beginning or end of a line
by xmath (Hermit) on Feb 23, 2003 at 00:19 UTC
|
Another strategy would be to simply do the subtitution on a substring that leaves out the first and last char:
substr($t, 1, -1) =~ s/"/""/g;
This however seems to be slower, according to a quick benchmark I've run (zwa = 'zero-width assertion', sub = 'substr'):
| [reply]
[d/l]
[select] |
|
|
Interestiing. Though I think your conclusion regarding "if the speed difference isn't relevant" is wrong. For instance, your test data had 2 "'s in a short string. By the time this gets to 10 "s the speed advantage swings the other way. Even more interesting is that if the string has 0 "s, using substr to avoid the zwa's is twice as fast.
Why would you run it on a string that had no "s. Well, if you're processing large volumes of data into a database from a flat file then you need to check each line as you go. Some will have embedded "s but many won't. You could make the s/// conditional by checking first with m// or index, but just checking causes another pass to be made and costs more than using the simpler regex on the substr.
As Abigail said (again) recently, and has rightly pulled me up for before, you have to check a range inputs before you can make a judgement.
I love Perl. There's always another nuance to explore.
..and remember there are a lot of things monks are supposed to be but lazy is not one of them
Examine what is said, not who speaks.
1) When a distinguished but elderly scientist states that something is possible, he is almost certainly right. When he states that something is impossible, he is very probably wrong.
2) The only way of discovering the limits of the possible is to venture a little way past them into the impossible
3) Any sufficiently advanced technology is indistinguishable from magic.
Arthur C. Clarke.
| [reply]
[d/l] |
|
|
OK, I kinda assumed that he gave a "typical" example of an input string, but you're right ofcourse - I should have tested across a wide range of values.
I should actually never have involved benchmarks, since the execution time of the subtitution is likely to be negligable compared to other parts of his application (whatever it may be), and is furthermore dependent on the input strings, on the platform, perl version, and possibly the phase of the moon.
But well, too late for that, the thread is now filled with benchmarks.. my fault :-)
| [reply] |
|
|
Goodie. A benchmark without the code. How utterly pointless.
Without knowing the code, the size of the data, and the number
of double quotes in it, your benchmark is nothing more than
a jumble of numbers.
It carries no meaning.
Abigail
| [reply] |
|
|
Ehm, I benchmarked the code I gave on the example data he gave ofcourse..
my $t = q["string (12" or 1 foot or 1') into 6" MySQL varchar"];
$t =~ s/(?<=.)"(?=.)/""/g;
versus
my $t = q["string (12" or 1 foot or 1') into 6" MySQL varchar"];
substr($t, 1, -1) =~ s/"/""/g;
And the number of iterations is shown in the benchmark output (2**20 = 1048576)
| [reply]
[d/l]
[select] |
|
|
Well, all of the discussion about benchmarks set me to wondering which was the most efficient. So here are the actual results. Bear in mind that the benchmarks were only repeated twice and it is a working server. The times where obtained by...
$ticks = time();
....
$ticks = time() - $ticks;
print "$count Records updated in $ticks seconds\n";
The program is moving data from one MySQL database to another. The subroutine gets passed a hash_ref to a map and a hash_ref from the source database as returned by $query->fetchrow_hashref. The map looks like...
my(%map) = qq( 'AMNT_PAID', '0.0',
'AMOUNT', '{PurchAmt}',
'DELIVERY_NOTE', '"{Delivery}"',
'EXPORTED', 'IF({Exported}, "Y", "N")',
'FUNDS', 'IF("{CompanyID}"="BCU", "USD"
+, "CAD")');
The makemap sub returns a string that can be used in the SET portion of an INSERT sql statement. The code snippet follows.
# Create a set list from the data and the data map
sub makemap {
my($map, $r) = @_;
my($key, $result, $t, %h, $ndx);
%h=%$r;
$result="";
foreach $key (sort(keys(%$map))) {
#print "$key:\t$map->{$key}\n";
$t = $map->{$key};
# substitute the actual value for the placeholder fieldname
$t =~ s/(\{\S+\})/defined(eval("\$h$1")) ? eval("\$h$1") : "NULL
+"/eg;
# replace any double quotes with escaped doubles
if ((substr($t,0,1) eq '"') && (length($t) > 2)) {
# best 196 sec
# $ndx = 1;
# while( ($ndx = index($t, '"', $ndx)) != -1 and ++$ndx < len
+gth($t)) {
# substr($t, $ndx++, 0, '"');
# }
# original 198 sec
# for ($ndx=1; $ndx < length($t)-1; $ndx++) {
# if (substr($t,$ndx,1) eq '"') {
# substr($t,$ndx,1) = '""';
# $ndx++;
# }
# }
# substring 204 sec
# substr($t, 1, -1) =~ s/"/""/g;
# zero width assertion 201 sec
$t =~ s/(?<=.)"(?=.)/""/g;
}
#print "$t\n";
$result .= qq($key=$t ,);
}
chop($result); # remove trailing ,
return($result);
}
Averaged over two runs each with 17400 records with 750 "pathological" records (with embedded double quotes)the results seem to indicate the iterative method is faster than the regex way but I like the elegance of the regular expression.
| [reply]
[d/l]
[select] |
|
|
Re: Regex matching on anywhere but the beginning or end of a line
by OM_Zen (Scribe) on Feb 23, 2003 at 01:53 UTC
|
my $var = q("string (12" or 1 foot or 1') into 6" MySQL varchar")
+;
$var =~ s/(?<!^)"(?!$)/""/g;
print "[$var]\n";
__END__
["string (12"" or 1 foot or 1') into 6"" MySQL varchar"]
The look_ahead and look_behind negative assertion on ^ and $ shall do the match only in the other places of the string
| [reply]
[d/l] |
Re: Regex matching on anywhere but the beginning or end of a line
by fruiture (Curate) on Feb 23, 2003 at 00:58 UTC
|
Well, you can achieve that without regexp:
my $string = q{"string (12" or 1 foot or 1') into 6" MySQL varchar"};
local $_ = 1;
while( ($_ = index($string,'"',$_)) !=-1
and ++$_ < length($string)
){
substr($string,$_++,0,'"');
}
--
http://fruiture.de | [reply]
[d/l] |
|
|
Rate idx sub zwa
idx 73430/s -- -5% -15%
sub 77672/s 6% -- -10%
zwa 86374/s 18% 11% --
(Benchmarked the same way as this one) | [reply]
[d/l] |
|
|
my $string = q{"string (12" or 1 foot or 1') into 6" MySQL varchar"};
benchmark(
shift,
q{"string (12"" or 1 foot or 1') into 6"" MySQL varchar"},
'substr' => sub {
my $t = $string;
substr($t, 1, -1) =~ s/"/""/g;
$t
},
's///' => sub {
my $t = $string;
$t =~ s/(?<=.)"(?=.)/""/g;
$t
},
'index' => sub {
my $t = $string;
local $_ = 1;
while( ($_ = index($t,'"',$_)) !=-1
and ++$_ < length($t)
){
substr($t,$_++,0,'"');
}
$t
},
);
benchmark() is just a wrapper around Benchmark::cmpthese() that checks the results of the routines.
# run with perl 5.6.1
EXPECTED : "string (12"" or 1 foot or 1') into 6"" MySQL varchar"
index : "string (12"" or 1 foot or 1') into 6"" MySQL varchar"
s/// : "string (12"" or 1 foot or 1') into 6"" MySQL varchar"
substr : "string (12"" or 1 foot or 1') into 6"" MySQL varchar"
Benchmark: timing 1000000 iterations of index , s/// , subs
+tr ...
index : 19 wallclock secs (19.66 usr + 0.02 sys = 19.68 CPU) @ 5
+0813.01/s (n=1000000)
s/// : 19 wallclock secs (19.85 usr + 0.00 sys = 19.85 CPU) @ 5
+0377.83/s (n=1000000)
substr : 23 wallclock secs (24.13 usr + 0.00 sys = 24.13 CPU) @ 4
+1442.19/s (n=1000000)
Rate substr s/// index
substr 41442/s -- -18% -18%
s/// 50378/s 22% -- -1%
index 50813/s 23% 1% --
# and with perl 5.8.0
EXPECTED : "string (12"" or 1 foot or 1') into 6"" MySQL varchar"
substr : "string (12"" or 1 foot or 1') into 6"" MySQL varchar"
index : "string (12"" or 1 foot or 1') into 6"" MySQL varchar"
s/// : "string (12"" or 1 foot or 1') into 6"" MySQL varchar"
Benchmark: timing 1000000 iterations of index , s/// , subs
+tr ...
index : 18 wallclock secs (18.71 usr + 0.01 sys = 18.72 CPU) @ 5
+3418.80/s (n=1000000)
s/// : 33 wallclock secs (31.67 usr + 0.02 sys = 31.69 CPU) @ 3
+1555.70/s (n=1000000)
substr : 35 wallclock secs (35.24 usr + 0.01 sys = 35.25 CPU) @ 2
+8368.79/s (n=1000000)
Rate substr s/// index
substr 28369/s -- -10% -47%
s/// 31556/s 11% -- -41%
index 53419/s 88% 69% --
--
http://fruiture.de | [reply]
[d/l]
[select] |
|
|
Re: Regex matching on anywhere but the beginning or end of a line
by BrowserUk (Patriarch) on Feb 23, 2003 at 05:30 UTC
|
$str = '"the "quick" brown "fox" jumps o""er "the" lazy "dog""';
substr($str,1,-1) =~ s/"/""/g;
print $str;
"the ""quick"" brown ""fox"" jumps o""""er ""the"" lazy ""dog"""
Examine what is said, not who speaks.
1) When a distinguished but elderly scientist states that something is possible, he is almost certainly right. When he states that something is impossible, he is very probably wrong.
2) The only way of discovering the limits of the possible is to venture a little way past them into the impossible
3) Any sufficiently advanced technology is indistinguishable from magic.
Arthur C. Clarke.
| [reply]
[d/l] |
Re: Regex matching on anywhere but the beginning or end of a line
by steves (Curate) on Feb 23, 2003 at 00:13 UTC
|
In a similar vein, you could match the characters before and after using a character class that says "anything but quote", then put those into the replacement string like this:
$x =~ s/([^"])"([^"])/$1""$2/g;
| [reply]
[d/l] |
|
|
Eh, no, multiple consecutive quotes is probably perfectly legal, and your substitution wouldn't handle them right.
| [reply] |
|
|
| [reply] |
Re: Regex matching on anywhere but the beginning or end of a line
by Aristotle (Chancellor) on Feb 24, 2003 at 12:57 UTC
|
s{
(?<!\A) # "beginning of string" doesn't match before current char
"
(?!\z) # "end of string" doesn't match behind current char
}
{""}xg;
See perlre.
That said, take dws's advice. Trying to manually quote all dangerous characters will get you in trouble because inadvertantly you'll forget some edge case. Even if not, metacharacters or edge cases may be introduced or removed in later versions of the database engine, breaking your quoting effort. The database driver is always kept in sync with the engine - let it do its job.
Makeshifts last the longest. | [reply]
[d/l] |