Regex sql Where condition
-
What should be my Regex to match fetch the
WHERE
condition without(GROUP\\ BY|HAVING|ORDER\\ BY|ASC|DESC|LIMIT)
? It works fine if I have any of the described params. It should also work for both the cases (with or without these parametersI 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);
-
@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
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.
-
@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