PHP 正则表达式:从查询字符串中删除顺序 - Php regex: remove order by from query string

Php regex: remove order by from query string

我有一个php方法，它应该从mysql查询字符串中删除"Order by"mysql语句的所有出现。

示例 1：

字符串：SELECT * FROM table ORDER BY name

结果：SELECT * FROM table

示例 2：

字符串：SELECT a.* FROM (SELECT * FROM table ORDER BY name, creation_date) AS a ORDER BY a.name

结果：SELECT a.* FROM (SELECT * FROM table) AS a

我现在的问题是：如何实现这一目标。

我尝试了以下方法：

if (stripos($sql, 'ORDER BY') !== false) {
    $sql = preg_replace('/'sORDER' BY.+/i', '', $sql);
}

但这适用于示例 1，但不适用于示例 2

您可以使用的

正则表达式是ORDER BY.*?(?='s*LIMIT|')|$)。

示例代码：

$re = "/ORDER BY.*?(?=''s*LIMIT|'')|$)/mi"; 
$str = "SELECT * FROM table ORDER BY name'n'nSELECT a.* FROM (SELECT * FROM table ORDER BY name, created_at) AS a ORDER BY a.name'n'nSELECT t0.* FROM table t0 WHERE t0.created_at IS NOT NULL ORDER BY t0.name, t0.created_at, t0.status LIMIT 10 OFFSET 10"; 
$result = preg_replace($re, "", $str);

演示

ORDER BY.*?(?=')|$)

试试这个。替换为 empty space 。请参阅演示。

https://regex101.com/r/tJ2mW5/22

$re = "/ORDER BY.*?(?='')|$)/mi";
$str = "SELECT * FROM table ORDER BY name'nSELECT a.* FROM (SELECT * FROM table ORDER BY name, creation_date) AS a ORDER BY a.name";
$subst = "";
$result = preg_replace($re, $subst, $str);