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

Hi monks, I have a small problem with a regex. I'm trying to pull headers out of a select clause in a sql statement. Everything was going fine until I threw myself a loop. 
$sql = qq|
  select 'First' = first_name, 'Last' = last_name
  from table
|;
# I pull the info from the select clause like this...
$sql =~ /select(.*?)from/i;

# Then split on comma.
@select = split (/,/, $1);

[download]
Here is my problem. I want to then split out the First and Last as headers in an array. The above example is simple as splitting on the comma. But when I get to examples like this... 
$sql = qq|
  select 'First' = coalesce(first_name, ''),
    'Last' = coalesce(last_name, '')
  from table
|;

[download]
As you probably noticed, the comma inside the coalesce will mess with my split. So, I guess what I want to do is split on the commas, as long as they are not contained inside a set of parenthesis. I am lost on how to accomplish this with a regex. Any help would be greatly appreciated.
Thanks.

Replies are listed 'Best First'.
Re: Pull info from select clause.
by Fletch (Bishop) on May 11, 2004 at 20:36 UTC

    Try SQL::Statement. Trying to parse arbitrary SQL with just a regex is probably just as good an idea as parsing HTML with a regex.

[reply]
Re: Pull info from select clause.
by dave_the_m (Monsignor) on May 11, 2004 at 21:41 UTC
    If you've got reasonable control over what SQL you have to handle, you could try first excising the text between matching pairs of parentheses, innermost first, eg 
    $sql = 'a=f(g(1,2),h(3,4,5),6), b=h(1)';
1 while $sql =~ s/  \(  [^(]*?  \)  //x;
print $sql, "\n";

    [download]
    which outputs 
    a=f, b=h

    [download]
[reply]
[d/l]
[select]
      That's exactly what I was looking for dave_the_m. Thanks for pointing me in the right direction. I just could not figure out how to do that with a split. That was my problem, I was trying a split alone without looping through the sql string and pulling out individual parts, which is what your while loop does. Thanks again.
[reply]
Re: Pull info from select clause.
by PodMaster (Abbot) on May 11, 2004 at 20:48 UTC
    So, I guess what I want to do is split on the commas, as long as they are not contained inside a set of parenthesis. I am lost on how to accomplish this with a regex.
    This is easily accomplished with a regex (and the m// operator), but not so easy with split. What you want to do is parse SQL, and the best way to do that is not to do it :) meaning use the CPAN or SQL::Statement.

    update: stricken extraneous bs. split or m// its all the same, you still have to parse the SQL.

    MJD says "you can't just make shit up and expect the computer to know what you mean, retardo!"
    I run a Win32 PPM repository for perl 5.6.x and 5.8.x -- I take requests (README).
    ** The third rule of perl club is a statement of fact: pod is sexy.

[reply]
Re: Pull info from select clause.
by the_0ne (Pilgrim) on May 11, 2004 at 20:57 UTC
    Thanks for the replies, but these queries are going to be in a controlled environment. I can make sure they look a certain way syntax-wise. Just can't get rid of functions like coalesce() or datepart.

    I installed the SQL::Statement module and tried it out, however, it's choking on the sql syntax. I hate to say it but this is a Sql Server 6.0 db I am working with. The query I am using for my testing works fine when run on the sql server, however, SQL::Statement does not like it and it choking with this error:

    SQL ERROR: Bad set function before FROM clause.

    It looks like this module expects a certain syntax, but I can't break my query just to make it work with the module. I'll work with the module some more, but so far not seeming to like my query syntax.
[reply]

Back to Seekers of Perl Wisdom