Regex (grep) for multi-line search needed

Question

Hi all

I'm running a grep to find any *.sql file that has the word select followed by the word customerName followed by the word from. This select statement can span many lines and can contain tabs and newlines.

I've tried a few variations on the following:

$ grep -liIr --include="*.sql" --exclude-dir="\.svn*" --regexp="select[a-zA-Z0-
9+\n\r]*customerName[a-zA-Z0-9+\n\r]*from"

This, however, just runs forever. Can anyone help me with the correct syntax please?

Answer 1

grep works line by line, you can't use grep on multiline.

man grep :

grep, egrep, fgrep, rgrep - print lines matching a pattern

Answer 2

Isn't grep only for single line search? Maybe this question is useful for you: How can I search for a multiline pattern in a file ?

Answer 3

Your fundamental problem is that grep works one line at a time - so it cannot find a SELECT statement spread across lines.

Your second problem is that the regex you are using doesn't deal with the complexity of what can appear between SELECT and FROM - in particular, it omits commas, full stops (periods) and blanks, but also quotes and anything that can be inside a quoted string.

I would likely go with a Perl-based solution, having Perl read 'paragraphs' at a time and applying a regex to that. The downside is having to deal with the recursive search - there are modules to do that, of course, including the core module File::Find.

In outline, for a single file:

$/ = "\n\n";    # Paragraphs

while (<>)
{
     if ($_ =~ m/SELECT.*customerName.*FROM/mi)
     {
         printf file name
         go to next file
     }
}

That needs to be wrapped into a sub that is then invoked by the methods of File::Find.

Answer 4

Hi, I am not very good in grep. But your problem can be solved using AWK command. Just see

awk '/select/,/from/' *.sql

Above code will result from first occurence of "select" till first sequence of "from". Now you need to verify whether returned statements are having "customername" or not. For this you can pipe the result. And can use awk or grep again.

Answer 5

Aside from the regex, you may want to look into ack, which automatically excludes the .svn directories for you, and which can limit to only .sql files with --sql.