1

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?

3
  • 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, 2015 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. Apr 30, 2015 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, 2015 at 17:23

2 Answers 2

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.

6
  • 1
    More simply seq 200 | awk '/50/{c=0}/50/,/70/ || c++==5'
    – Costas
    Apr 30, 2015 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, 2015 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, 2015 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. Apr 30, 2015 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? Apr 30, 2015 at 14:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .