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

Hi, I would like to know how to search for text(say a SQL statement with Bind Variables) from an input file and output the same to different text file. Basically, the perl script should search text(i.e. a SQL statement with Bind Variables) which occurs many times in that file and should output each text(the SQL statement) found into many text files. Eg. Output file should be named SQL1.txt, SQL2.txt, SQL3.txt....etc. where SQL1 is the output file which contains the first occurence to a SQL Statement.

Replies are listed 'Best First'.
Re: Search a text and output to different text files
by davorg (Chancellor) on Jul 22, 2005 at 10:31 UTC

    This assumes that your search text won't be across different lines.

    open SEARCH, $file_to_search or die $!;

my $count = 1;

while (<SEARCH>) {
  if (/$regex_to_search_for/) {
    open FOUND, '>', "found$count.txt" or die $!;
    print FOUND $_;
    ++$count;
  }
}

    [download]
    --
    <http://www.dave.org.uk>

    "The first rule of Perl club is you do not talk about Perl club."
    -- Chip Salzenberg

[reply]
[d/l]
    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: Search a text and output to different text files
by tlm (Prior) on Jul 22, 2005 at 12:27 UTC

    Here's an adaptation of davorg's code to handle the multiline search. 

    open SEARCH, $file_to_search or die $!;
my $text = do { local $/; <SEARCH> };
close SEARCH;

my $count = 1;

while ( $text =~ /($regex_to_search_for)/sg ) {
  open FOUND, '>', "found$count.txt" or die $!;
  print FOUND $1;
  ++$count;
}

    [download]
    The above is intended as Perl-ish pseudocode to give you an idea of what to do. The devil is in the details, in particular, in $regex_to_search_for. E.g. I added the /s attribute in case it included a "."; it may need additional or different attributes, but you probably want to keep the /g.

    the lowliest monk

[reply]
[d/l]
Re: Search a text and output to different text files
by jhourcle (Prior) on Jul 22, 2005 at 12:31 UTC

    A sample of the text that you're attempting to match (preferably multiple occurances, so we can see how they're saved within the file, as there may be clues for seperators. etc.)

    I mean, when I think 'bind variables', I think of having the parameters seperate from the SQL statement, but it's possible that you're using the term to suggest that there are values embedded in the SQL statement.

[reply]
      Sample output is shown as code for only SELECT SQL but there would be many SQL's like SELECT, UPDATE, DELETE AND INSERT. Also is it possible to get only the unique SQL's. The output should contain only the SQL with Bind Variable. It should display the junk values as shown below. 
          ***** SQL Statement *****

SELECT T0.ROW_ID C0, T0.MODIFICATION_NUM C1 FROM siebel.S_SEC_OBJECT T
+0 WHERE ((T0.OBJ_TYPE_CD = ?) AND (T0.NAME = ?)) FETCH FIRST 1 ROWS O
+NLY OPTIMIZE FOR 1 ROW

***** Bind Variables *****

Bind variable 0: User
Bind variable 1: sadmin

[7/14/05 23:19:56:108 MDT] 00000072 SystemOut     O 2005-07-14 23:19:5
+6,108    TRACE_HIGH    DBAxsSql    sadmin    69f1985c-b880-4c52-af59-
+e12add06c461.35    uuid:ba3bbb25-e0d9-55b0-7d71-5d60253944cc    uuid:
+d5f09b24-6116-4f9b-8825-d6d136c66098    ***** SQL Statement Execute T
+ime: 0 ms *****

      [download]
[reply]
[d/l]

        Yes, it's possible, but it might be a little more work, as given the earlier requirements, you need to keep track of which sql statement is in which file.

        It's almost easier to just write the code, than to try to explain the logic in english.

        Read more... (2 kB)
[reply]
[d/l]

Back to Seekers of Perl Wisdom