Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Pull info from select clause.

by the_0ne (Pilgrim)
on May 11, 2004 at 20:26 UTC ( #352583=perlquestion: print w/ replies, xml ) Need Help??
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);
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 |;
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 (Chancellor) 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.

Re: Pull info from select clause.
by dave_the_m (Parson) 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";
    which outputs
    a=f, b=h
      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.
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.

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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://352583]
Approved by Enlil
Front-paged by castaway
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (6)
As of 2015-08-01 08:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found
    past polls