CODEWITHSUNDEEP
phpmysqlsqlregexpreg-match
Pnia
Pnia

Reputation: 63

preg_match extract identifier and alias

I'm trying to extract identifier and alias from mysql ORDER BY and closest question it can help me is Removing aliases from a SQL select statement, using C# and regular expressions

function test($orderby)
{
    if(preg_match('/(?<field>.*)(?:\s*|\s+AS\s+)?(?<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"); works

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

test("table.field"); works

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

test("CONCAT(table.field1, ' ', table.field2) AS alias"); doesn't work

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

test("table.field alias"); prints

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

I need in example 3 [field] => CONCAT(table.field1, ' ', table.field2) AND [alias] => alias and in example 4 [field] => table.field AND [alias] => alias

What i'm trying to do is

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

What i'm doing wrong? Thanks in advance.

Upvotes: 0

Views: 1004

Answers (1)

fusion3k
fusion3k

Reputation: 11689

This pattern will work for all your examples:

/(?<field>.*?)((?:\s+|\s+AS\s+)(?<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 demo

New capturing group is created, so you have to unset also $matches[3]:

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

Since we add the endline anchor, I suggest you to add this line at the begin of the function:

$orderby = trim( $orderby );

Upvotes: 3

Related Questions