Beefy Boxes and Bandwidth Generously Provided by pair Networks Frank
Perl-Sensitive Sunglasses
 
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
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 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.
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.

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 scrutinizing the Monastery: (10)
As of 2014-04-17 11:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (446 votes), past polls