Remove last order by clause from SQL Query using php regex

Question

I have following query

select id,(ROW_NUMBER() OVER (ORDER BY (SELECT 1))) AS  SerialNo,name,address from customer order by name

Now i want to remove last order by from query while keeping SerialNo. Here i will have multiple queries so order by field will be changed yet it will be only single field.

I tried to use the Php regex: remove order by from query string answer yet it removes all the order by from the string which is not what i want.

Thank you in advance.

http://xyproblem.info/ What is the X problem to the Y that you've posted? — Sammitch, Dec 23 '19 at 01:01
[`preg_replace('/(.+) order by.*/i',"$1",$sql)`](https://tio.run/##PY1NC4JAFEX3/oqHCM7UoLi2iCyhRamMfdBKzHmloI3N2KJfP@nGzT1cOJfb170xq012yCzL0Z8W1mBrbLEaoBGM8PRWJJdTFHNCIb3GHEjK9yOiO5A8Psa7MwSUUtjmADmqpmwTyd5lh6wUQqHW8FSyg@qrB9mhAqnEmI8fTI4dzqe9wlehsG/LConrE29JZ9db@C6zncBmk0zHEVa1hKmExvwB) to cut off the string at last `order by` — bobble bubble, Dec 23 '19 at 01:47
@bobblebubble that won't work if there's a `LIMIT` clause after the `ORDER BY` — Nick, Dec 23 '19 at 02:04
@bobblebubble i also want to get that order by field rather then just remove it. How is it possible? — The N World, Dec 23 '19 at 03:28
If you have at least 2 ORDER BY and no succeeding string after the last one, then you can try this `/.*(order by .*)/i` , it can capture all characters after the last ORDER BY as a group and then you can replace it. — Simon, Dec 23 '19 at 07:18

score 1 · Accepted Answer · answered Dec 23 '19 at 17:32

The problem is detecting the end of an order by clause in the general case. These are some examples:

select * from (select * from t order by x, y) sq
select * from t order by x limit 1
select * from t order by (select 1)

Here I am working on the assumption that the order by clause you are interested in is at the end of the sql statement and includes everything through the end of that statement. Under that assumption:

Regex Demo

<?php

$sql = 'select id,(ROW_NUMBER() OVER (ORDER BY (SELECT 1))) AS  SerialNo,name,address from customer order by name';

if (preg_match('/.*(\border\s+by\s+.*)/is', $sql, $matches, PREG_OFFSET_CAPTURE)) {
    $orderBy = $matches[1][0];
    $offset = $matches[1][1];
    $newSql = substr($sql, 0, $offset);
    echo "The order by clause was: '$orderBy'.\n";
    echo "The new sql is: '$newSql'.\n";
}

Prints:

The order by clause was: 'order by name'.
The new sql is: 'select id,(ROW_NUMBER() OVER (ORDER BY (SELECT 1))) AS  SerialNo,name,address from customer '.

Remove last order by clause from SQL Query using php regex

1 Answers1