Help with regex for commas in sql query

Question

Hi, I have a SQL query like:

select column1 as a, DECODE(x, 1, x1, 2, x2) as column2, 
    DECODE(y, 1, y1, 2, y2) as column3 from test

And I want to select the following commas (denoted with **,**):

select a as column1 **,** DECODE(x, 1, x1, 2, x2) as column2 **,** 
    DECODE(y, 1, y1, 2, y2) as column3 from test

I am using /(?!(.*\))),|,(?=.*\()/gs for that, but how can you see in http://gskinner.com/RegExr/ the commas in first DECODE are selected too. I need that regex for parse columns in my select used in this code:

$select = substr($sql, 6, strpos($sql, "from") - 6);
$parts = preg_split('/(?!(.*\))),|,(?=.*\()/s', $select);

$columns = array();
foreach($parts as $p) {
    @list($id, $alias) = preg_split('/( as )/', $p);
    $columns[trim($id)] = trim($alias);
}

Answer 1

Have a try with this :

<?php

$sql = "select a as column1, DECODE(x, 1, x1, 2, x2) as column2, DECODE(y, 1, y1, 2, y2) as column3 from test";

$select = substr($sql, 6, strpos($sql, "from") - 6);
$select .=',';
$parts = preg_split('/ as (.*?),/s', $select, NULL, PREG_SPLIT_DELIM_CAPTURE);
$columns = array();
for($i=0; $i<count($parts)-1; $i+=2) {
  $columns[trim($parts[$i])] = $parts[$i+1];
}
print_r($columns);

?>

Update:

For more complex queries, you should use a SQL parser.