preg_match提取标识符和别名

Question

我正在尝试从mysql ORDER BY中提取标识符和别名，它可以帮助我的最接近的问题是使用 C# 和正则表达式从 SQL 选择语句中删除别名

function test($orderby)
{
    if(preg_match('/(?<field>.*)(?:s*|s+ASs+)?(?<alias>w*)?/i', $orderby, $matches)){
        unset($matches[1]);
        unset($matches[2]);
        echo '<pre>'.htmlspecialchars(print_r($matches,true)).'</pre>';
    }else{
        echo '$orderby doest not matches';
    }
}

test("field");作品

Array
(
    [0] => field
    [field] => field
    [alias] => 
)

test("table.field");作品

Array
(
    [0] => table.field
    [field] => table.field
    [alias] => 
)

test("CONCAT(table.field1, ' ', table.field2) AS alias");不起作用

Array
(
    [0] => CONCAT(table.field1, ' ', table.field2) AS alias
    [field] => CONCAT(table.field1, ' ', table.field2) AS alias
    [alias] => 
)

test("table.field alias");打印

Array
(
    [0] => table.field alias
    [field] => table.field alias
    [alias] => 
)

我需要在示例 3 中[field] => CONCAT(table.field1, ' ', table.field2)和[alias] => alias，在示例 4 中需要[field] => table.field和[alias] => alias

我想做的是

/
(?<field>.*)             #identifier
(?:s*|s+ASs+)?        # without spaces (examples 1 and 2), spaces (example 4) OR 'AS' (example 3)
(?<alias>w*)?           #alias
/i

我做错了什么？提前谢谢。

Answer 1

此模式适用于所有示例：

/(?<field>.*?)((?:s+|s+ASs+)(?<alias>w+))?$/i
            │ │     │          ┊          │ │┊│
            1 2     3          4          5 267
1) Added   not-greedy operator;
2) Added   capturing group for sub-groups AS/alias;
3) Changed zero-or-more to one-or-more (at least one space is needed);
4) Removed zero-or-one for subgroup AS (at least one space is needed);
5) Changed zero-or-more to one-or-more (at least one char is needed);
6) Moved   zero-or-more from sub-group alias to group 2);
7) Added   end-of-line anchor.

3v4l.org 演示

将创建新的捕获组，因此您还必须取消设置$matches[3]：

unset( $matches[1], $matches[2], $matches[3] );

由于我们添加了结束线锚点，我建议您在函数的开头添加此行：

$orderby = trim( $orderby );