mjhjmtu

I am trying to use the awk range pattern to find all SQL select statements in a group of files, inspired by this stackoverflow answer.

From the awk manual:

The pattern1, pattern2 form of an expression is called a range pattern. It matches all input records starting with a record that matches pattern1, and continuing until a record that matches pattern2, inclusive.

My initial attempt was

awk '/select/,/from/' *

where * in this case just represents a large number of varied files.

This returned several false hits on HTML select tags, so I refined my command to

awk '/[^<]select[^>]/,/from/' *

which seems to have eliminated most of those hits.

However, I still get some false hits from occurrences of the word "select" in a comment, and those hits produce very many lines of noise each before they eventually hit a "from" or the end of the file.
What I would like is for the range pattern not to register a match if there are more than, say, 10 lines between the "select" and the "from".

My question is: Can I make the range pattern fail to match if the number of lines between the match of pattern1 and the match of pattern2 exceeds a given threshold, and if so, how?

You can expand the /pattern1/,/pattern2/ condition as much as you want, by adding a block after to be performed when this occurs:

See for example how we print those numbers being between 50 and 70, but just the first 5 matches of each block:

$ seq 200 | awk '/50/,/70/ if ($0~/50/) c=0; if (c++ <= 5) print'
50
51
52
53
54
55
150
151
152
153
154
155

In your case, you may want to say something like this, that will print the first 10 lines that were matched.

awk '/[^<]select[^>]/,/from/ if (c++ <= 10) print' *

A more complex solution would consist in storing all this output and then printing it at the END block. This way, you can control the block itself instead of just a specific line. I would do this storing the data in an array, etc.

Range patterns are useful but not flexible. Instead of using them, maintain the between-or-not state in a variable. The awk script /select/,/from/ is equivalent to

/select/ printing = 1
printing print
/from/ printing = 0

If you want to limit the range to a number of lines, maintain a counter of lines seen and accumulate the output until you've decided whether to display it.

/select/ select_text = $0; select_line_count = 1;
select_line_count select_text = select_text "n" $0
/from/ if (select_line_count <= 10) print select_text; print
 select_line_count = 0

You'll probably want to refine the pattern, for example to require that select is at the beginning of the line except for whitespace, and is followed by whitespace: /^[t ]*select($|[t ])/