使用Regex [PHP]拆分SQL列名及其别名中的任何一个

Question

Using Regex I want to separate the column names (if any) from their aliases. As I mentioned below, I created an expression but it does not work without quotes.

Thank you !

(?<nameFull>               #nameRegex
 (?<name>[^"'`]+)          #name
 (?<nameQoute>["'`]+)?      #name Qoute
)
(?<aliasFull>               #aliasRegex
 \s+                        #before Alias WhiteSpace
 (?<AS>AS\s+)?              #AS Is match or Not match
 (?<aliasQoute>["'`])?      #alias Qoute
 (?<alias>[^"'`]+)          #alias
)?

You can also see the test I made with this link.

Answer 1

Here is a regex matching your needs :

^\W?(?<table>[\w-]+\.)?(?<name>[\w-]+)\W?(\W+as\W+)?(?<alias>[\w-]+)?\W?$

Test this regular expression online

PHP snippet

$re = "/^\W?(?<table>[\w-]+\.)?(?<name>[\w-]+)\W?(\W+as\W+)?(?<alias>[\w-]+)?\W?$/i";

$array = [
    "columnam-e as myalias",
    "table.columnam-e as myalias",
    '"column" AS "mycolumn"',
    "'column' AS 'mycolumn'",
    "'table.column' AS 'mycolumn'",
    "`column` AS `mycolumn`",
    "columnam-e mycolumn",
    "colunmanesingle"
];

foreach ($array as $i => $value) {

    preg_match($re, $value, $matches);

    $name = $matches['name'];
    $table = NULL;
    $alias = NULL;

    if(array_key_exists('table', $matches)) {
      $table =  $matches['table'];
    }
    if(array_key_exists('alias', $matches)) {
      $alias =  $matches['alias'];
    }

    printf("%s=> table: %s, name: %s, alias: %s", $value, $table, $name, $alias);
}

Test this PHP snippet online