Regex sql Where condition

chilarai

I need to get id=1 or id=2

QString query = "SELECT * FROM users WHERE id=1 or id=2"
QString whereString;  

QRegularExpression whereListRegex("\\ WHERE\\ (.*?)\\ (GROUP\\ BY|HAVING|ORDER\\ BY|ASC|DESC|LIMIT)\\ ", QRegularExpression::CaseInsensitiveOption);
QRegularExpressionMatch whereIterator = whereListRegex.match(query);

JonB

@chilarai
Looks like you want:

QRegularExpression whereListRegex("\\ WHERE\\ (.*?)(\\ (GROUP\\ BY|HAVING|ORDER\\ BY|ASC|DESC|LIMIT)\\ )?", QRegularExpression::CaseInsensitiveOption);

I don't know about all your backslashing and spaces. I also think what you have is not right/good enough. But the important thing here to answer your question is that in a reg ex

(...)?

means "0 or 1 occurrences of what is inside the parentheses to the left of the question mark", i.e. ? means preceding is optional.

Just to be clear: you will not be able to robustly parse a SQL statement like this using regular expressions. I can easily come up with legitimate queries which it will mis-parse. Your approach will work if you have a limited number of cases you wish to support, so long as that OK in the context where you want to use this.

chilarai

@JonB said in Regex sql Where condition:

("\ WHERE\ (.*?)(\ (GROUP\ BY|HAVING|ORDER\ BY|ASC|DESC|LIMIT)\ )?",

Your solution is not working.

Also, if what I am doing is wrong can you point me to the right way to matching the conditions

JonB

@chilarai
My answer will work based on what you had. However, as I said, if that is not good enough/wrong (which I see as the case) it will not improve the situation. For example, you have specified your "optional group" as ending in \\ ). That would mean it can only match (be optional) if there is that space at the end. If you don't have that, it won't match.

As a by-the-by, there is no need to protect the spaces with \, and it just makes your expression harder to read. I think

" WHERE (.*?) (GROUP BY|HAVING|ORDER BY|ASC|DESC|LIMIT) "

would do the same as what you presently have.

You might simplify your current to something more like:

" WHERE (.*)( GROUP|ORDER|LIMIT)?"

[EDIT: Hmm, that may not work, it should work for the plain WHERE id=1 or id=2 case; but I think the (.*) will include any e.g. GROUP ..., and leave the ( GROUP|ORDER|LIMIT)? capture group empty, which is not what you want.... This will require a more advanced reg ex, with some "not"-ting... See my adjustment in my next post below.]

I am not going to go through all the possibilities of what you might want here. That is for you to do, I suggest you go to a site like https://regex101.com/ to test your expression as you design.

However, I have said that no regular expression will correctly parse a SQL condition. So nobody can tell you what to write, until you define what limited subset of SQL you wish to support.

chilarai

@JonB Thanks for the suggestion

JonB

@chilarai
It seems to me: you want to "stop at" any GROUP|ORDER|LIMIT. I think your original (.*?) --- "non-greedy" --- was correct. So my "very simple" suggestion would indeed be:

" WHERE (.*?)( GROUP|ORDER|LIMIT)?"

chilarai

@JonB Thanks. With your help and some others, I have finally narrowed down my query to

QRegularExpression whereListRegex(R"(\sWHERE\s+(.*?)(?:\s+(?:GROUP|ORDER|LIMIT)\b|\s*$))", QRegularExpression::CaseInsensitiveOption);

This works for both the conditions I have asked for