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

Hi Monks,

A simple little questions if I may:

I would like to parse an SQL string to find the alias names used.

Since alias names are (in my SQL atleast) in the form of 'clients as c, staff as s' it is easy enough to split on ' as ' and find the next word and push it into a list so I end up with 'a' and 's' as two elements of a @list.

My question is, can this be done as a one-liner without having to build one list then loop through that building the final list?

Thanks, 

 ___
/\__\ Creative Object World COW. 
\/__/ www.wolispace.com/cow   "What is the world coming to?"

[download]

Replies are listed 'Best First'.
Re: Simple little regex to build a list
by merlyn (Sage) on Feb 14, 2005 at 04:59 UTC
[reply]
Re: Simple little regex to build a list
by davido (Cardinal) on Feb 14, 2005 at 05:01 UTC

    If you really want to use split, you could do it like this: 

    my $flop = 1;
my @list = grep { $flop *= -1; $flop > 0 } 
             split /\s+as\s+|\s*,\s+/, $string;

    [download]

    It seems pretty fragile though, and more kludgy than a regexp with the /g modifier in list context. You're splitting on 'as' (with leading and trailing whitespace), and on comma, with optional leading whitespace, and mandatory trailing whitespace. Every second item is assumed to be a value that you wish to keep. It could break if you have 'as' in any of the keys, and it could break in a lot of other ways too, but for your immediate definition of the problem, it works.


    Dave

[reply]
[d/l]
Re: Simple little regex to build a list
by Errto (Vicar) on Feb 14, 2005 at 05:03 UTC

    Assuming valid aliases must match \w+, you can say 

    my @aliases = $sql =~ /\bas\s+(\w+)/ig;

    [download]
    The /i is necessary because SQL is generally case-insensitive. Note that this may return false positives if, eg, your query has a string literal with the word "as" in it, but it's a first approximation. Global (/g) match in list context returns the list of patterns matching the group (\w+). perlretut goes into this also.

    Also, I don't know if this is standard or not, but some databases allow the keyword "as" to be omitted, so you can say 

    SELECT foo a, bar b FROM baz

    [download]

[reply]
[d/l]
[select]
      Ah.. That is the little bit of perl wisdom I was missing: 
      my @aliases = $sql =~ /\bas\s+(\w+)/ig;

      [download]
      I didn't know I could build up a list from the result of a regex.. but I sort of hoped I could.

      Its the little ways things can be combined that makes perl so much fun.

      The good news for all you worried monks is that I am only looking at the tables section of the SQL (I break my SQLs into $tables, $field, $where and $order)

      The other good news is that I alway use 'as' in this particular application.

      So good news all around!

      Thanks monks.

      PS I try not to use modules as I mostly work on virtual hosts and I dont have that much control. 

       ___
/\__\ Creative Object World COW
\/__/ www.wolispace.com/cow "What is the world coming to?"

      [download]
[reply]
[d/l]
[select]
[reply]

Back to Seekers of Perl Wisdom