comparing 2 lists

dhudnall has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to compare two lists. I have read in two files and stored them as lists. one files contains a list of DB table names while another file contains a list of SQL statements. What I want to do is compare the two. If the list of DB table names match anything in the SQL file, I want to print the table name that was matched. This is the code I have written but doesn't work.

#----------------------------------------------------

#This section will contain the searched for the DB tables in the SQL f
+iles
#and return all lines that contain an instance of a DB table.


#This will open the file that will contain the list of tables
open(TABLES, '<DBtables1.txt') or die "The file DBnames.txt could not 
+be opened\
n";

#This will open the file that will contain the list of SQL statements 
+to be pars
ed
open(UPDATE, '<updateDan.txt') or die "The file updateDan.txt could no
+t be opene
d\n";

#This will open the file that will be written to containing the list o
+f tables
#in the SQL files

open(USED, '>>tableUsed.txt') or die "The file tablesUsed.txt could no
+t be writt
en to\n";

#This will convert the files into lists to be searched
@tableList=<TABLES>;
@fileList=<UPDATE>;
@tableUsed=<USED>;

#declarations
$W=0;
$Q=0;

#This will search through the files and see if the tables exist in the
+ file
foreach $W (@tableList)
{
        foreach $Q (@fileList)
        {
                #if (exists($Q{$W}))
                if($W==$Q)
                {
                        #print TABLE "$W\n";
                        push(@tableUsed, $W);
                        print USED @tableUsed;

                }#end of if
                else
                {
                        print USED "no tables exist in the file\n";
                }
        }
}#end of foreach loop


close(UPDATE);
close(TABLES);
close(USED);
[download]

#----------------------------------------------- I have tried several solutions as you can see with the commented lines that were once statements. Please if you know of a way I can do this, it will tremendously help me with my project. I am new to PERL and therefore am just beginning to learn the syntax and its capabilities. Thanks

Comment on comparing 2 lists Download Code

Replies are listed 'Best First'.
Re: comparing 2 lists by citromatik (Curate) on Jun 19, 2007 at 14:26 UTC
You can use hashes (as the perlfaq4 suggests): How do I compute the difference of two arrays? How do I compute the intersection of two arrays? `perdoc -q arrays` citromatik Update:i.e. you can try something like this: (not tested) `my %count = (); my @tableused = (); foreach $W (@tableList,@fileList){ $count{$W}++ }; foreach $elem (keys %count) { push @tableused, $elem if $count{$elem} > 1; }` [download] Update2:Your code seems syntactically valid, except for the equality test: `if($W==$Q)` If you are comparing strings you should use the "eq" op `if ($W eq $Q)`	[reply] [d/l] [select]
Re: comparing 2 lists by scorpio17 (Canon) on Jun 19, 2007 at 15:02 UTC
I think you might want to try something like this: `chomp @tableList; chomp @fileList; for $W (@tableList) { for $Q (@fileList) { if($Q =~ /$W/i) { print USED "$W\n"; } } }` [download] This will use a simple regular expression to see if the table name $W matches anything in the query string $Q. The /i makes it case insensitive (so "two" will match "Two" or "TWO" for example.) The chomps are needed to strip off the newline character "\n" at the end of each line, else the match will fail unless the table name appears at the end of the query. The matches will be printed to the output file, all others will be ignored. I hope this helps - good luck!	[reply] [d/l]
Re^2: comparing 2 lists by ikegami (Patriarch) on Jun 19, 2007 at 15:32 UTC
You've put the table loop on the outside, which is good. If it was on the inside, you'd be compiling a lot of regexps for nothing. But it can be taken a step further. `my $tableList = map qr/$_/i, join '\|', map quotemeta, @tableList; for my $Q (@fileList) { push @tableUsed, $Q =~ /$tableList/g; }` [download] The OP might want to remove duplicates from @tableUsed. `my $tableList = map qr/$_/i, join '\|', map quotemeta, @tableList; my %tableUsed; for my $Q (@fileList) { for my $W ($Q =~ /$tableList/g) { ++$tableUsed{$W}; } } my @tableUsed = sort keys %tableUsed;` [download]	[reply] [d/l] [select]
Re: comparing 2 lists by santhi (Monk) on Jun 20, 2007 at 07:30 UTC
There are two things which you need to modify. 1.The problem may be because you are using the "= =" operator in your code to compare. For string comparision you have to use "eq" like `$W eq $Q`. As the SQL statements can have other words than the table names you should go for pattern matching of table name instead of checking for equality. For Eg if SQL Stmt is as: `Select * from abc` If suppose you are searching for table name "abc" and you use equality operator, the strings will never match, so go for the regex. 2. Also you are pushing the table name found into an array and writing the whole array again. So by this there will be repetition of table names Instead of pushing into an array and writing that array to a file, just write the table name alone to the file as `print USED $W;` So code can be: `foreach $W (@tableList){ foreach $Q (@fileList){ if($Q =~ /$W/i){ print TABLE "$W\n"; push(@tableUsed, $W); print USED $W; }#end of if else{ print USED "no tables exist in the file\n"; } } }#end of foreach loop` [download]	[reply] [d/l] [select]