#!/usr/bin/perl 
use strict;
use warnings;

my $sql = <<END_SQL;
select the_date as "date",
round(months_between(first_date,second_date),0) months_old
,product,extract(year from the_date) year
,case
  when a=b then 'c'
    else 'd'
      end tough_one
      from XXX
END_SQL

my $lparen  = qr/\(/;
my $rparen  = qr/\)/;
my $keyword = qr/(?i:select|from|as)/; # this is all this problem needs
my $comma   = qr/,/;
my $text    = qr/(?:\w+|'\w+'|"\w+")/;
my $op      = qr/(?:=>|<=|[=+\-*\/])/;

sub lexer {
  my $sql = shift;
  return sub {
    LEXER: {
      return ['KEYWORD', $1] if $sql =~ /\G ($keyword) /gcx;
      return ['COMMA',   ''] if $sql =~ /\G ($comma)   /gcx;
      return ['OP',      $1] if $sql =~ /\G ($op)      /gcx;
      return ['PAREN',    1] if $sql =~ /\G $lparen    /gcx;
      return ['PAREN',   -1] if $sql =~ /\G $rparen    /gcx;
      return ['TEXT',    $1] if $sql =~ /\G ($text)    /gcx;
      redo LEXER             if $sql =~ /\G \s+        /gcx;
    }
  };
}

my $lexer = lexer($sql);
my @tokens;
my $nested = 0;
while (my $token = $lexer->()) {
  $nested += $token->[1] if 'PAREN' eq $token->[0];
  next if $nested or $token->[1] eq 'PAREN';
  last if 'KEYWORD' eq $token->[0] && 'FROM' eq uc $token->[1];
  push @tokens => $token;
}

foreach my $i (0 .. $#tokens) {
  my $token = $tokens[$i];
  next unless 'TEXT' eq $token->[0];
  print $token->[1], $/ if $i == $#tokens;
  print $token->[1], $/ if 'COMMA' eq $tokens[$i + 1][0];
}
__END__
# prints out:

"date"
months_old
product
year
tough_one