Geek asks and answers

Regular expression to select selected columns

I need to extract the column names from MYSQL SELECT, and I would like to do this with Regex.
It is simple SELECT, something like:
SELECT column1, column2 ... FROM table

I have to cover all cases, without our alias, with or without a table, with or without quoting char:

SELECT column, column as foo, table.column, table.column as foo, 
       `column`, `column` as foo, `table`.`column`, `table`.`column` as foo
       .....

Currently, I have managed to work out this regular expression: #\w+(\sas)?#ibut this is not as good as the prefix columns.
Any help?

By the way, is Regex doing a good job of this?

EDIT
Thanks for the answers! The templates you have selected are valid for the entire query, in fact I am already processing each column:

$fields = Frameworkmethod::getSelectFields($query);
$columns = explode(',' , $fields);
foreach($columns as $column)
{
     //do Regex work to "clean up" the single field and get the "standard" one (not the alias)
     //`#__tracktime_projects`.`pr_name` AS `project_name` should return pr_name
}

As stated in the comment above, I always need the field name, not an alias. Sorry for not indicating this before!

+5
5

Collapse Capture Repeating Pattern Regex .

, RegEx * SQL:

/(?:SELECT\s++(?=(?:[#\w,`.]++\s++)+)|(?!^)\G\s*+,\s*+(?:`?+\s*+[#\w]++\s*+`?+\s*+\.\s*+)?+`?+\s*+)(\w++)`?+(?:\s++as\s++[^,\s]++)?+/ig

-: http://regex101.com/r/wL7yA9

PHP- preg_match_all() RegEx, /x:

preg_match_all('/(?:SELECT\s++(?=(?:[\#\w,`.]++\s++)+) # start matching on SELECT
                |              # or
                (?!^)\G        # resume from last match position 
                \s*+,\s*+      # delimited by a comma 
                (?:`?+\s*+     # optional prefix table with optional backtick
                    [\#\w]++   # table name
                    \s*+`?+    # optional backtick
                    \s*+\.\s*+ # dot separator
                )?+ # optional prefix table end group

                `?+\s*+ # optional backtick

            ) # initial match or subsequent match

            (\w++)    # capturing group
            `?+         # optional backtick


            (?:\s++as\s++[^,\s]++)?+ # optional alias

            /ix', $query, $matches);

: http://codepad.viper-7.com/VTaPd3

. " " SQL

PHP- explode()

$columns = explode(',', $fields);

foreach($columns as $column)
{
    $regex='/([\w]++)`?+(?:\s++as\s++[^,\s]++)?+\s*+(?:FROM\s*+|$)/i';

    preg_match($regex, $column, $match);

    print $match[1]; // field stored in $match[1]
}

: http://codepad.viper-7.com/OdUGXd

+6

PHP:

$query = 'SELECT column1, column2 as foo, table.column3, table.column4 as foo, 
       `column5`, `column6` as foo, `table`.`column7`, `table`.`column8` as foo
       FROM table';

$query = preg_replace('/^SELECT(.*?)FROM.*$/s', '$1', $query); // To remove the "SELECT" and "FROM table..." parts

preg_match_all('/(?:
    (?:`?\w+`?\.)? (?:`)?(\w+)(?:`)? (?:\s*as\s*\w+)?\s*
#   ^--TableName-^ ^---ColumnName--^ ^----AsFoo-----^
)+/x',$query, $m);

print_r($m[1]);

:

Array
(
    [0] => column1
    [1] => column2
    [2] => column3
    [3] => column4
    [4] => column5
    [5] => column6
    [6] => column7
    [7] => column8
)

Live demo: http://www.rubular.com/r/H960NFKCTr

UPDATE:. "", SQL (: #__tracktime_projects), . , , , , , i, :

$query = 'SELECT column1, column2 as foo, table.column3, table.column4 as foo, 
       `column5`, `column6` as foo, `table`.`column7`, `table`.`column8` as foo, `#__tracktime_projects`.`pr_name` AS project_name, `#wut`
       FROM table';


$query = preg_replace('/^SELECT(.*?)FROM.*$/s', '$1', $query); // To remove the "SELECT" and "FROM table..." parts

$allowed = '\w#'; // Adjust this to the names that you expect.

preg_match_all('/(?:
    (?:`?['.$allowed.']++`?\.)?
#   ^--------TableName--------^

    (?:`)?(['.$allowed.']++)(?:`)?
#   ^----------ColumnName--------^

    (?:\s*as\s*['.$allowed.']++)?\s*
#   ^-------------AsFoo------------^
)+
/xi',$query, $m);

print_r($m[1]);

:

Array
(
    [0] => column1
    [1] => column2
    [2] => column3
    [3] => column4
    [4] => column5
    [5] => column6
    [6] => column7
    [7] => column8
    [8] => pr_name
    [9] => #wut
)

Live demo: http://www.rubular.com/r/D0iIHJQwB8

+2

PHP ( , ), getcolumnmeta. :

<?php
  $select = $DB->query('SELECT COUNT(*) FROM fruit');
  $meta = $select->getColumnMeta(0);
  var_dump($meta);
?>

:

array(6) {
  ["native_type"]=>
   string(7) "integer"
  ["flags"]=>
   array(0) {
  }
  ["name"]=>
   string(8) "COUNT(*)"
  ["len"]=>
    int(-1)
  ["precision"]=>
    int(0)
  ["pdo_type"]=>
    int(2)
}
0

:

((select|,)\s+((((`)?\w+\6?\.)?(`)?\w+\7?)(\s+as\s+(`)?\w+\9?)?\s*))+from\s

$3 .

- (?...) . .

. , \w + [a-zA-Z]\w *, .

0

, , , @CS.. ,

preg_match_all('/(?<=^SELECT |, |\) )([a-z]+\.)?([a-z]+ )?(as )?([a-z]+)(?= ?,|$)/im');

, , : https://gist.github.com/pedrosancao/2498ed85b3c1834c5bdd

0

More articles:


All Articles