How to find the condition of SQL WHERE clause pragmatically

I have created an API Gateway that will accept an SQL statement, run the statement against the database and return its result as a response. For every SQL statement, I receive as input I have to append a WHERE condition in the SQL. For example:-

Input SQL- SELECT * FROM contacts WHERE last_name = ‘Johnson’;

Append Condition- country = ‘India’

New SQL- SELECT * FROM contacts WHERE last_name = ‘Johnson’ AND (country = ‘India’);

For the above SQL, it was easy to find the WHERE keyword and its condition as it was at the end. But for some SQL we can have OFFSET, HAVING, ORDER BY, GROUP BY, FETCH NEXT and many more after the WHERE. So, cannot find a robust way to find out the WHERE condition and append to it.

Possible usage of WHERE is as below-

SELECT *                          // Condition With Spaces
FROM contacts
WHERE last_name = 'Johnson';      

SELECT *                          // Condition Without Spaces
FROM contacts
WHERE last_name='Johnson';    


SELECT *                          // Multiple Conditions
FROM suppliers
WHERE ((state = 'Florida' AND supplier_name = 'IBM')
OR (supplier_id > 5000)) AND       


SELECT email FROM emailTable       // WHERE With OFFSET
WHERE user_id=3
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;     


SELECT column_name(s)            // WHERE With GROUP BY, HAVING, ORDER BY  
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

One approach would be to list down all the keywords that come after WHERE to find its condition. But I want to check if there is any better approach for this. Any suggestions or documents will be appreciated.

Answer

Use subqueries:

select q.*
from (<query>) q  -- insert query here
<where>           -- insert other code here

So, this query becomes:

SELECT q.*
FROM (SELECT * 
      FROM contacts
     ) q
WHERE last_name = 'Johnson';

If you find that you also need to append an ORDER BY/OFFSET, I would recommend passing that in as a separate argument — as you do with the WHERE.

Advertisement

Answer