Part2: Regular Expressions in AWK

Please read Part1: Introduction to AWK if you are new to using awk Let's consider awk.input as the input file for the examples we are going to look at in this post.

cat awk.input
SQL> select distinct 'alter tablespace ' || tablespace_name || ' end backup;' from v$backup, dba_data_files where v$backup.status='ACTIVE' and v$backup.file#=file_id;

'ALTERTABLESPACE'||TABLESPACE_NAME||'ENDBACKUP;'
-----------------------------------------------------------
alter tablespace PSAPUNDO end backup;
alter tablespace SYSAUX end backup;
alter tablespace PSAPSIDDB end backup;
alter tablespace PSAPSID640 end backup;
alter tablespace PSAPSID end backup;
alter tablespace PSAPSIDUSR end backup;
alter tablespace SYSTEM end backup;

SQL> spool off

This file is the (altered) spool output of the endbackup script.

To print the lines containing the word alter:

awk '/alter/ { print }' < awk.input
SQL> select distinct 'alter tablespace ' || tablespace_name || ' end backup;' from v$backup, dba_data_files where v$backup.status='ACTIVE' and v$backup.file#=file_id;
alter tablespace PSAPUNDO end backup;
alter tablespace SYSAUX end backup;
alter tablespace PSAPSIDDB end backup;
alter tablespace PSAPSID640 end backup;
alter tablespace PSAPSID end backup;
alter tablespace PSAPSIDUSR end backup;
alter tablespace SYSTEM end backup;

In the above output, except for the first line, all the lines are valid SQL commands. I just need the lines that begin with alter.

To print the lines beginning with the word alter:

awk '/^alter/ { print }' < awk.input
alter tablespace PSAPUNDO end backup;
alter tablespace SYSAUX end backup;
alter tablespace PSAPSIDDB end backup;
alter tablespace PSAPSID640 end backup;
alter tablespace PSAPSID end backup;
alter tablespace PSAPSIDUSR end backup;
alter tablespace SYSTEM end backup;

Alternatively, I can look for lines that end with the word backup;

To print the lines ending with the word backup;:

awk '/backup;$/ { print }' < awk.input
alter tablespace PSAPUNDO end backup;
alter tablespace SYSAUX end backup;
alter tablespace PSAPSIDDB end backup;
alter tablespace PSAPSID640 end backup;
alter tablespace PSAPSID end backup;
alter tablespace PSAPSIDUSR end backup;
alter tablespace SYSTEM end backup;

To print lines that contain PSAP or SYS:

awk '/PSAP|SYS/ { print }' < awk.input
alter tablespace PSAPUNDO end backup;
alter tablespace SYSAUX end backup;
alter tablespace PSAPSIDDB end backup;
alter tablespace PSAPSID640 end backup;
alter tablespace PSAPSID end backup;
alter tablespace PSAPSIDUSR end backup;
alter tablespace SYSTEM end backup;

To print lines that contain SAPAUX or SYSAUX:

awk '/(SAP|SYS)AUX/ { print }' < awk.input
alter tablespace SYSAUX end backup;

To print line containing any of the characters S, A or P:

awk '/[SAP]/ { print }' < awk.input
SQL> select distinct 'alter tablespace ' || tablespace_name || ' end backup;' from v$backup, dba_data_files where v$backup.status='ACTIVE' and v$backup.file#=file_id;
'ALTERTABLESPACE'||TABLESPACE_NAME||'ENDBACKUP;'
alter tablespace PSAPUNDO end backup;
alter tablespace SYSAUX end backup;
alter tablespace PSAPSIDDB end backup;
alter tablespace PSAPSID640 end backup;
alter tablespace PSAPSID end backup;
alter tablespace PSAPSIDUSR end backup;
alter tablespace SYSTEM end backup;
SQL> spool off

Here is a useful list of regular expressions:

[X-Zx-z]           Matches for X, Y, Z, x, y or z
[^ABC]            Matches for characters other than A, B and C
[ABC][XYZ]    Matches for A, B or C followed by X, Y or Z
[ABC]?            Matches for empty string and lines containing A, B or C