The general problem with the regex is that . matches any character (because you've used /s), and that .+ is by default greedy, that is, it will match as many times as possible. Have a look at perlretut (pay attention to mentions of "greed").

One way to solve this is to change the dot . to an expression that says "match anything except this". For single characters, one could use a negated character class, as in [^a-z]* which will match any characters except lowercase ASCII letters. For longer strings, one way to do it is with a negative lookahead: (?:(?!not|these|strings).)* will give you the "hard stop" you want.

You've only provided one example string, but when developing regular expressions, the more test cases the better. I showed one way to write tests for regexes here: Re: How to ask better questions using Test::More and sample data. For now I've guessed a few test cases, but you should fill that out. This also works on your long string:

use warnings;
use strict;
use Test::More;

my $regex = qr{
        \b INSERT \b
        (?:  # the next thing may not be one of these:
            (?! \b SELECT \b | \( | \; ) .
        )+   # match one or more times
        \b doc \b  # target string
    }imsx;

  like "INSERT doc (",          $regex;
  like "INSERT INTO doc bar (", $regex;
unlike "INSERT foo ( doc",      $regex;
unlike "INSERT foo SELECT doc", $regex;
unlike "INSERT foo ; doc",      $regex;

done_testing;
[download]

Update before posting: Eily already posted something very similar here, I'll post this anyway. Update: Adding \b is a good point. Update 2: Minor edits for clarity.

Edit: actually I think holli's answer is better. It's just easier to use a tool designed specifically for SQL rather than try to parse the request and try to think of all the edge cases.

You should switch to Ruby and use the (?~ ) construct which makes solving your problem easier. While it is possible to solve your problem in a single regex, using more advanced features, there's also the simpler solution of dividing the problem in more simple steps. For example you could first extract the string between INSERT and either ( or SELECT, and then search for DOC in that substring.

# Untested
my $ScanString =~ /
                       (                         # start of capture   
+   
                         INSERT
                         .*?                     # the ? after * means
+ the shortest possible match
                       )                         # end of capture
                       (?:\(|values|select|;)      # (?: thing ) group
+s without capturing
                     /six; # s allows .* to match across several lines
+. x allows spaces and comments in the regex
my $substring = $1;

my $found_doc = $substring =~ /\b doc \b/ix; # \b means boundary, so b
+eginning or end of a word
[download]

BillB:

I'd suggest placing "\s" just before and after doc in your regex, so it will accept doc as a word instead of as a word fragment.

Since you only want to match it if it precedes '(', 'SELECT' or ';', another way to do it would be to split your string on any of those tokens before doing your check, so any subselect or statement following your insert would be excluded from your match.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

SQL::Parser

This was my first thought as well, to use a parser instead of a regex whenever possible. Unfortunately, it turned out that it doesn't seem to work. I checked the SQL::Statement::Syntax docs and they say that only INSERT statements of the form INSERT INTO $table [ ( $col1, ..., $colN ) ] VALUES ( $val1, ... $valN ) are supported, and "inserting from a subquery is not currently supported". Posting the code here anyway, maybe I missed something.

Read more... code (2 kB)

If you have Perl version 5.10+ regex extensions, you might also consider the  (*COMMIT) backtracking control verb. Used in conjunction with (*FAIL), this can produce a "hard" stop if a pattern is found that's as hard as you could ever want.

Hi,

doc.+
[download]

doc[,\s]
[download]

Hope this helps!