regex question -- looking for ? that is NOT inside of quotes

[B Mayes]

I have never been the best at regular expressions, so I'm hoping someone here can help. I have some SQL statements inside of String objects in Java. What I would like to do is drop any part of a WHERE clause that has a parameter in it like this:

SELECT * FROM SOME_TABLE WHERE VALUE=?;


I could check to see if the string contains the character '?' but what if it's a literal inside of a string like this?

SELECT * FROM SOME_TABLE WHERE VALUE='?';


In such a case I don't want to drop the WHERE clause just because it contains ? -- since ? is a character inside of the string. The WHERE clauses here are pretty specific and generally only contain constructs like AND and OR. There's nothing like a WHERE EXISTS and then correlated subqueries or anything. So my idea was to obtain the substring of everything after the word "WHERE", then split on AND and OR. Now with a set of strings to look through, I need to see if any of them contain a parameterized statement. If so, I would like to simply replace that particular portion of the WHERE clause with empty string (i.e. -- get rid of it!).

So any ideas how to match a String with ? that is *not* surrounded by single quotes? Thus, these strings should get matched (or at least the bold parts):

- VALUE=?
- VALUE=? AND OTHER=50


and these should not:

- VALUE='?'
- VALUE='abc?123'

Thanks!


EDIT: I'm thinking that the argument to matches should be something like:

.*=\s*[^']*[?][^']*.*

But what if they have single quotes repeated (to escape the quote and use it as part of the string) like 'don''t touch that'? Hmm...

[Ninad Kulkarni]

I don't know why you want to drop any part of a WHERE clause that has a parameter in it.
Following regular expression may be suitable for your purpose.

"\\w+=\\?"

[B Mayes]

Thanks I will try that. I know it doesn't sound like it makes any sense but in this one context of this application it actually does. Just trust me. ;)

[Stephan van Hulst]

What about Associativity? If you drop part of a WHERE clause, you will have to figure out which ANDs and ORs to drop as well, etc. This will not be a very easy task. Why are you trying to do this?

[B Mayes]

Stephan van Hulst wrote:What about Associativity? If you drop part of a WHERE clause, you will have to figure out which ANDs and ORs to drop as well, etc. This will not be a very easy task. Why are you trying to do this?

I agree this isn't the easiest thing in the world, but it is still possible. I don't think figuring out the correct AND is going to be terribly difficult anyway. I'll try to get it working on Monday or Tuesday -- we'll see.

I don't mean to be rude or ungrateful but the motivation behind this seems irrelevant to me. Suffice it to say that it's for something at work. I got a bug request right before a big demo that our PMs/sales guys were doing. We wound up simulating the desired behavior by using a DB trigger on a particular table, but there is one valid case where arguments are not passed from JavaScript to the server (Java). In such a case I would like to just drop the offending portion of the WHERE clause.

Of course, now that I have this task in front of me I feel like I need to conquer it so I'm not going to give up on it...even if we don't ultimately wind up using it at work. Just FYI the following seems to work quite well (though I'll do some more rigorous testing to confirm). It's a slight modification of the above regex by Ninad. Thank you!

String regex = ".*\\w+\\s*=\\s*\\?$";

[B Mayes]

By the way, I went ahead and marked this as solved because the regex I posted above seems to be working great. I committed the change last week and haven't heard anything wrong from test yet. Essentially, I just split on " AND " since clauses from multiple places are always ANDed together, and then I build it back up with a StringBuilder object. The method is quite simple. Here is the basic idea:

    private String cleanWhereClause(String sql) throws TRDIException
    {
		// regex is anything, then at least 1 word character, then any number of spaces, then '=', then any number of spaces,
		// then a '?' anchored at the very end of the string
		String regex = ".*\\w+\\s*=\\s*\\?$";
		String afterWhere = sql.substring(sql.indexOf("WHERE")+6);
		String[] clauses = afterWhere.split(" AND ");
		StringBuilder sb = new StringBuilder(sql.substring(0, sql.indexOf("WHERE") + 6));
		
		// add only the non-matching portions of the WHERE clause
		for (int i = 0; i < clauses.length; i++)
		{
			String curr = clauses[i].trim(); 
			if (!curr.matches(regex))
				sb.append(curr + (i == clauses.length-1 ? "" : " AND "));
		}
    }

An interesting observation is that if the word AND appears inside of a string literal, the method still returns the appropriate information. Note that I am splitting on " AND " which is padded by spaces. So a string like "FOO='BRAND'" will not get split up while a string like "BAR='ROCK AND ROLL!' will get split -- but it will be pieced back together in the exact same manner.

I know everyone thinks I am insane for doing this but there is one case where this is applicable, and the above code is working out great thus far.