I'm not sure I've got this completed narrowed down, but please bear with me...

I've got a multi-line SQL SELECT statement generated by a tool where I want to extract the table name. Okay, easy enough. Here's simple code which plucks out the table name when there is a WHERE clause and when there is not: 

#!/usr/bin/perl

my $stmt0 = "SELECT table.field0, table.field1\nFROM table;\n";
my $stmt1 = "SELECT table.field0, table.field1\nFROM table\nWHERE fiel
+d0 = 'foo';\n";

my $tablename = $1 if $stmt0 =~ m/^.*FROM\s+(\S+)\s*.*;$/s;
print "table name =\"$tablename\"\n";

$tablename = '';
$tablename = $1 if $stmt0 =~ m/^.*FROM\s+(\S+)\s*.*;$/s;
print "table name =\"$tablename\"\n";

[download]

I get the same answer either way, I would think that I've got a generalized regular expression which takes care of all types of SELECT statements.

Or so I thought...

When I run this regular expression to extract the table name in the script which accesses the tool, the tool emits a multi-line statement with no WHERE clause, and does not match anything with the above regular expression. If I take the semicolon out of the RE: 

$tablename = $1 if $stmt =~ m/^.*FROM\s+(\S+)\s*.*$/gs;

[download]

I get the table name as table;. I can hack around this problem by adding a substitution afterwards: 

$tablename = $1 if $stmt =~ m/^.*FROM\s+(\S+)\s*.*$/gs;
$tablename =~ s/;//;

[download]

...but this only points out that I don't know what's going on.

I then dumped the statement to a file and wrote a small C program to read the string character-by-character to see what really was in it. In stepping through execution with a debugger, I saw nothing out of the ordinary -- the SELECT keyword, two fully-qualified field names, a 13,10 newline character pair given that this is on Windows followed by the FROM clause.

I'm baffled as to why I need the kludgy substitution. Any insight any of you can provide would be appreciated. Thanks.

20031203 Edit by Corion: Changed PRE tags to CODE tags

In reply to regular expression across lines? by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.