Find SQL SELECT statements in files

animalet · Aug 25, 2005#12005-08-25T16:15+00:00

Hi,

I'm trying to find in a folder (find in files) all the text documents containing a SELECT statement wich contain concrete fields in WHERE clause, but the more I try, the more I fail.

The regex i tried last time, after some hours of thinking and searching very carefully was this:

select[\s\r\n\w\W\D]+TABLENAME([\s\r\n\w\W\D]*FIELD1|FIELD2)*

This simply doesn't work, neither each expressions tried before.

The FROM clause is not specified because of I'm sure that the field names are unique in database.

Can anybody help me??

Than you in advance.

Mofi · Aug 27, 2005#22005-08-27T14:32+00:00

Give us a small example, how one of the files with SELECT looks like and explain which criteria you want for the find expression.

animalet · Aug 29, 2005#32005-08-29T11:41+00:00

Mofi wrote:Give us a small example, how one of the files with SELECT looks like and explain which criteria you want for the find expression.

They are PL/SQL code files (packages, functions and no-named blocks).

The criteria is simple, I want to find out what files have SELECT statements with a concrete table and/or field are involved. Example:

We have table A with fields A1 and A2.

If a file has SELECT statements with the element A (in the FROM clause), and/or at least one of the elements A1 or A2 (in the WHERE clause), I want the regexp to recognise it. These SELECT statements have no defined format because they are build by different people without a convention, so we need to consider line breaks (CR/LF), tabs, and similars.

Thank you for your patience.

Mofi · Aug 29, 2005#42005-08-29T11:59+00:00

I'm not familiar with PL/SQL code. As long as you not able to post an example(s), I can't help you.

animalet · Aug 29, 2005#52005-08-29T12:56+00:00

This could be an example of SELECT statement which I would like to identify inside a text file, looking for SELECT's which contain the word 'A' inside the FROM clause and the word 'A1' or 'A2' in the WHERE clause.

Code: Select all

SELECT   A.A1 AS FIELD1,
              B.B1 AS FIELD2 --This is a one-line comment
FROM TABLE_A A,
         TABLE_B B
-- One-line comments can be everywhere
WHERE A.A1 = B.B1 + TO_NUMBER(B.B2)
     AND A.A2 LIKE B.B3
/*This is a 
multi-line comment*/
ORDER BY FIELD1; /*end of the statement*/

Mofi · Sep 05, 2005#62005-09-05T12:10+00:00

I was very busy last week and this is a real hard one. A possible solution for this find in UltraEdit style is following regular expression. I don't have experience in Unix style regular expressions, so if you prefer Unix style translate it yourself.

SELECT[~|]+%FROM* A[~|]+%WHERE*.^{A1^}^{A2^}

Explanation:

- Find SELECT.

- [~|]+ = followed by any character including line endings except | (| should not exist in whole file; if exist, use an other character). This expression is very aggresive. It will not stop on first occurence of next word, but on last one. So don't use this expression for replace.

- Followed by string FROM at start of line (%). You can remove % if FROM is not always at column 1.

- Followed by any character at same line until a space and first word of interest for you (word A in this example) is found.

- [~|]+ = see above.

- Followed by string WHERE at start of line (%). You can remove % if FROM is not always at column 1.

- Followed by any character at same line until a point and one of the two words of interest for you (word A1 or A2 in this example) is found.