search a word from file

Shell Programming and Scripting
1

Hello,
I have a file which contains some SQL statements. I need to take out the table name from the file. Table name will always end with "_t". can anyone help me in getting that?
for e.g.
---
SQL_STMT do_sql_insert: cmd="insert into account_t ( poid_DB, poid_ID0, poid_TYPE, poid_REV, business_type, account_type, name, account_no,
SQL_STMT dm_search_robj: cmd="select poid_DB, poid_ID0, poid_TYPE, poid_REV from bal_grp_t where bal_grp_t.account_obj_ID0 = :1
SQL_STMT do_sql_update: cmd="update account_t set poid_rev = poid_rev + 1, mod_t = :mod_t, currency=:currency, account_no=:account_no
---
Thanks in advance.

2

Something like ...

grep -v "_t" FILENAME

... obviously this will also match ...

UPDATE table set col="welcome to _town" ... ;

... you should / could use your favorite diff to inspect your changes ...

grep -v "_t" FILENAME >FILENAME2
diff FILENAME FILENAME2
3

this will give me the lines which does not contain "_t". what i want is the table name ....
so from my example i shld get...
account_t
bal_grp_t
account_t

4 
#!/usr/bin/perl
#
# cat SQLFILENAME | perl CODEFROMBELOWFILE.pl
#

use strict;

my %words;

while(my $line = <STDIN>)
{
   # munge line here to just deal with (select|delete) .* from (.*) (where|order by|group by|having) issues

    for my $word (split(/\s+/, $line))
    {
        $words{$word}++ if($word =~ m/^\w+_t$/i);
    }
}

for my $k (sort keys %words)
{
    print "$k\n";
}
5

well you could try something like:

sed 's/.* \(.*_t \).*/\1/g' infile

or

tr " " "\n" <infile | grep _t$

but since you have things like mod_t in there, I'm guessing without a bit better parsing it won't be quite as exact as you'd like - may be good enough though depending on your overall sample size / whether this is a one-off etc

6

Thanks for the solution...
but i m new to shell scripting... i m not able to understand this code.
can u pl. explain where i have to pass my file name and how to execute this code?

7

save the code as a file e.g. perl-script

chmod the file to be exacutable.

then the following will work:

#  cat infile | ./perl-script
account_t
bal_grp_t
mod_t

FYI - this gives the same o/p as the sed cmd I suggested above...

8

What about mod_t? That is not a table name so the criteria for deciding what is a table name needs to be clearly defined.

9

No problem even if mod_t comes in my result.
other things is sql will be for insert, update or select...

10

Give this a try:

awk '
{ for(i=1;i<=NF;i++) {
    if($i ~ /into|from|"update/) { 
      print $(i+1)
    }
  }
}' file

If you get errors, use gwk, nawk or /usr/xpg4/bin/awk on Solaris.

Regards