CODEWITHSUNDEEP
sqlregexparsingwhere-clauseconditional-statements
LarZuK
LarZuK

Reputation: 826

An regex to extract SQL where clause

First, I confess I'm not realy experimented with regular expressions. I know how use it but when I want to build one, it's something else... I'm going to document me.

I want to extract the WHERE clause in a SQL query. My goal is to be able to add an condition, like this:

SELECT * FROM myTbl WHERE columnA = 'B' AND columnB = 'C' ORDER BY columnX GROUP BY columnZ LIMIT 5

TO :

SELECT * FROM myTbl WHERE columnC = 'D' AND (columnA = 'B' AND columnB = 'C') ORDER BY columnX GROUP BY columnZ LIMIT 5

I tried some expression but I'm so void...

(where (.*)(?<=order by))

I wanted to get all between 'where' and ('order by' or 'limit' or 'group by')...

Anyone have an advice for me ? I have done some search and I don't find anything like this. I found SQL Parser but these engines are too big compared to the task I want to complete.

Thank you.

Upvotes: 2

Views: 7384

Answers (3)

Lucero
Lucero

Reputation: 60190

Since the WHERE clause can be quite complex (including subqueries which may include an ORDER BY in some cases, for instance when used with FOR XML), so you will not be able to really find an always reliably working solution with a regex.

A better solution would be to use a proper SQL parser which generates an AST, and then you can just extract the WHERE clause from that. For T-SQL, you could use the parser from the bsn ModuleStore project (LGPL license). Modifying the AST is easy and you can re-script the statement afterwards.

Upvotes: 2

Ren&#233; Nyffenegger
Ren&#233; Nyffenegger

Reputation: 40489

This might get you going:

declare
  sql_stmt varchar2(4000) := q'!SELECT * FROM myTbl WHERE columnA = 'B' AND columnB = 'C' ORDER BY columnX GROUP BY columnZ LIMIT 5!';

  where_stmt varchar2(4000) ;

begin
  where_stmt := regexp_replace(sql_stmt, '.*(WHERE.*?)ORDER BY.*', '\1');

  dbms_output.put_line(where_stmt);

end;
/

The scriplet above will output WHERE columnA = 'B' AND columnB = 'C'.

Upvotes: 0

Dennis Kreminsky
Dennis Kreminsky

Reputation: 2089

You're using lookbehind (?<=) whereas you need lookahead (?=) assertion.

There's more on that.

Upvotes: 1

Related Questions