1

I have a php method, which should remove all occurences of an "order by" mysql statement from a mysql query string.

Example 1:

STRING: SELECT * FROM table ORDER BY name

RESULT: SELECT * FROM table

Example 2:

STRING: SELECT a.* FROM (SELECT * FROM table ORDER BY name, creation_date) AS a ORDER BY a.name

Result: SELECT a.* FROM (SELECT * FROM table) AS a

My question now is: How to achive this.

I have tried the following:

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

But this would work for example 1, but not for example 2

bernhardh
  • 3,137
  • 10
  • 42
  • 77

2 Answers2

4

Regex you can use is ORDER BY.*?(?=\s*LIMIT|\)|$).

Sample code:

$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);

Demo

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • 1
    Nope. Your regex removes the end of the string of example 2 as well. see https://regex101.com/r/hC0pF7/1 . The answer of vks works – bernhardh Mar 24 '15 at 11:39
  • I have updated to `[\\w.]+` that will only match letters, numbers, "_" and dots. – Wiktor Stribiżew Mar 24 '15 at 11:40
  • neither your demo, nor your sample code work. in the demo, you produce broken sql code, and your sample code doesn't replace anything at all – bernhardh Mar 24 '15 at 11:42
  • Sorry, I see now what you meant. I have updated the regex, code and link in the answer. – Wiktor Stribiżew Mar 24 '15 at 11:48
  • thank you. you got +1, but I have already accepted vks answer – bernhardh Mar 24 '15 at 11:49
  • :) I also upvoted your question. If you think that suggestion is what you need, it's OK. I just wanted to add that if performance matters, consider this speed comparison: `(?i)order by ([\w., ]+)` finishes in 18 + 21 + 15 steps, and `ORDER BY.*?(?=\)|$)` finishes in 32 + 78 + 36 steps. – Wiktor Stribiżew Mar 24 '15 at 11:52
  • @stribizhev no. of steps is never a measure of performance.read here for more info http://stackoverflow.com/questions/26093501/atomic-groups-clarity – vks Mar 24 '15 at 12:08
  • @stribizhev also your regex will break if query is `SELECT a.* FROM (SELECT * FROM table ORDER BY name, c@#reation_date) AS a ORDER BY a.name` ie it has `#@` etc.So this way is fragile :) – vks Mar 24 '15 at 12:20
  • @vks: Database column names do not usually contain such symbols. – Wiktor Stribiżew Mar 24 '15 at 12:28
  • Since neither your, nor vks code works in some cases, I need your help again - see https://regex101.com/r/jS5fN1/1 – bernhardh Mar 25 '15 at 16:44
2
ORDER BY.*?(?=\)|$)

Try this.Replace by empty space.See demo.

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);
vks
  • 67,027
  • 10
  • 91
  • 124
  • Yes, I had to wait some minutes because of SO, so that I can accept it. But now, I have accepted it. – bernhardh Mar 24 '15 at 11:48
  • Ok, your regex isn't working correct in all cases. Please have a look at https://regex101.com/r/jS5fN1/1 - here its removing the limit and the offset as well :/ – bernhardh Mar 25 '15 at 16:38
  • @vks The above breaks when you include direction, e.g. `ORDER BY name DESC, created_at` – rmirabelle Aug 26 '16 at 16:39