• Post Reply Bookmark Topic Watch Topic
  • New Topic

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

 
B Mayes
Ranch Hand
Posts: 47
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:



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
Ranch Hand
Posts: 802
Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
B Mayes
Ranch Hand
Posts: 47
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Saloon Keeper
Posts: 7993
143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 47
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!

 
B Mayes
Ranch Hand
Posts: 47
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:




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.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!