2

I have to search for a multi-line pattern in my sql script using awk.

Sample script-

select * from dept where department_name in ('abc'
, 'xyz', 'def') ;

In the above example I have to search for the pattern department_name in upto the closing bracket ) and there can be multiple occurrences of this pattern in the script.

The values in the 'IN' clause will span multiple-lines in the above example.

5
  • you want to search pattern only inside the ( ) ??
    – Kamaraj
    Commented Feb 10, 2017 at 6:59
  • No, i want to search from "department_name" to the first closing bracket ")" and this can span across multiple lines. Commented Feb 10, 2017 at 7:01
  • can you give the multiple line example and which pattern you want to match and what is the expected output
    – Kamaraj
    Commented Feb 10, 2017 at 7:09
  • I have given an example in the question. Commented Feb 10, 2017 at 7:12
  • your example didn't match your comment ----- and this can span across multiple lines
    – Kamaraj
    Commented Feb 10, 2017 at 7:14

3 Answers 3

3

Assuming the SQL uses ; as statement delimiters:

$ cat statements.sql
First bit;
Second thing;

More
of the same;

select * from dept where department_name in ('abc'
, 'xyz', 'def') ;

Getting to the end;

The
End;

Then you may use ; as the record separator in awk and just match as usual:

$ awk 'BEGIN { RS=";" } /department_name in ([^)]+)/ { print $0 ";" }' statements.sql


select * from dept where department_name in ('abc'
, 'xyz', 'def') ;

The pattern, /department_name in ([^)]+)/ matches the string department_name in ( followed by one or several characters that are not ) (including newlines), followed by a single ).

The print statement adds ; to the end of the record since that ; was removed by awk (it removes all record separators).

You'll get a few extra blank lines since there were blank lines after the preceding ; in the file. You can filter them out with sed -n '/[[:graph:]]/p':

$ awk 'BEGIN {RS=";"} /department_name in ([^)]+)/ { print $0 ";" }' statements.sql |
  sed -n '/[[:graph:]]/p'
select * from dept where department_name in ('abc'
, 'xyz', 'def') ;
4
  • can u explain how this pattern works "department_name in ([^)]+)" Commented Feb 10, 2017 at 8:37
  • @PankajPandey Updated the answer.
    – Kusalananda
    Commented Feb 10, 2017 at 8:39
  • but as far as I know the default behaviour of awk is that it does pattern matching line by line (one single line at a time).. then how can your regex span multiple lines. Commented Feb 10, 2017 at 8:45
  • @PankajPandey It reads records, and the default record separator (RS) is a newline. I'm changing the record separator to ;. Newlines becomes just another character.
    – Kusalananda
    Commented Feb 10, 2017 at 8:47
2

You can use awk and the record separator RS:

$ cat << EOT | awk -v RS="department_name|)" 'NR%2==0'
> select * from dept where department_name in ('abc' , 
> 'xyz', 
> 'def') ;
> EOT
in ('abc' , 
'xyz', 
'def'

RS is set to either department_name or ), this way the record can be composed with multiple lines.

Provided that you don't have twice same tag, NR%2=0 will print everything in between the 2 different tags.

0

Hope this is the answer you searching for:

grep -oE 'department_name.*\)' file_name

P.S do not know awk solution to this.

You must log in to answer this question.

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