2

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?

  • To address your "false hits for 'select' in comments" issue you'd have to refine your pattern. I suppose 'select' is at the beginning of the line (with optional whitespace)? - Then a pattern range /^[[:space:]]*select/,/from/ should help. (You probably need a refinement like that for the /from/ part as well.) – Janis Apr 30 '15 at 12:04
  • @Janis: I would, except I want to match all the select statements in all the files given, and there is no guarantee that they all conform to such conventions. It is highly possible that a few statements start after some other non-SQL code on a line. – Anders Rabo Thorbeck Apr 30 '15 at 14:13
  • But then there would be some syntactical delimiter (like a semicolon), wouldn't it? - So I'd expect you could likely match something like /(^|;)[[:space:]]*select/,/from/ then. – Janis Apr 30 '15 at 17:23
1

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 ])/

0

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.

  • 1
    More simply seq 200 | awk '/50/{c=0}/50/,/70/ || c++==5' – Costas Apr 30 '15 at 11:58
  • Cool, it works! But I don't understand how this || c++==5 works and to "what" it is doing the "or" operation. Could you expand it? – fedorqui Apr 30 '15 at 12:07
  • 1
    c++==... is equal c==... ; c=c+1. /70/ is equal $0 ~ /70/. So after , we will have ifpattern $0 ~ /70/ OR c == 5 with then add 1 to c – Costas Apr 30 '15 at 12:27
  • @fedorqui: Thank you, this is useful, but not exactly what I'm looking for. For a limit of 10, this will print 10 lines of noise if no match is found for pattern2, as opposed to no lines printed which I would prefer. I am a novice at awk, so there is probably a way to buffer the lines from the match of pattern1 until either reaching a match of pattern2 and outputting them all, or exceeding the line threshold and discarding all the lines of that failed match. However, I do not yet know enough awk to express that. – Anders Rabo Thorbeck Apr 30 '15 at 14:07
  • @Costas: Thank you for the improvement to @fedorqui's suggestion. I don't quite understand what is happening in the pattern1 of your range pattern, i.e. /50/{c=0}/50/. Why is /50/ specified twice? Is it valid to write an action in between two regular expression patterns? – Anders Rabo Thorbeck Apr 30 '15 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.