Pull info from select clause.

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.

Comment on Pull info from select clause. Select or Download Code

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

$sql = 'a=f(g(1,2),h(3,4,5),6), b=h(1)';
1 while $sql =~ s/  \(  [^(]*?  \)  //x;
print $sql, "\n";
[download]

a=f, b=h
[download]

[reply]
[d/l]
[select]

Re: Re: Pull info from select clause.

by the_0ne (Pilgrim) on May 12, 2004 at 01:34 UTC

dave_the_m

[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.~~

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

[reply]


go ahead... be a heretic
	PerlMonks