Regular expression parsing sql filter

Question

I'm trying to parse apart the different filter strings for an sql server WHERE clause, and I thought that regular expressions would be the way to go. Fortunately, the filter will always be flat, there will be no "sub-filters". Each filter statement will always be surrounded in parenthesis. Here's an example of a string I'd like to parse:

([IsActive]=(1)) AND ([NoticeDate] >= GETDATE()) AND (NoticeDate <= DATEADD(day, 90, GETDATE()))

The result I'd like is an array with the following items:

[0] = ([IsActive]=(1)) AND
[1] = ([NoticeDate] >= GETDATE()) AND
[2] = (NoticeDate <= DATEADD(day, 90, GETDATE()))

The closest I've come is the following regex:

/\(.+?\) (and|or)/i

but this only returns

[0] = ([IsActive]=(1)) AND
[1] = AND

So basically I'd like to return anything surrounded in parenthesis, followed by a space, followed by the string "and" or "or". The last statement will not be followed by and/or, though I could concatenate a " and" string if that would make it easier. This is being done using classic asp JScript. I've pretty much exhausted my (weak) regular expression abilities, any help would be greatly appreciated. Thanks in advance

Kirill Polishchuk · Accepted Answer · 2012-02-08 19:22:30Z

3

Try this regex:

(?i)\(.+?\)(?:\s+(?:and|or)|$)

Output:

enter image description here

answered Feb 8, 2012 at 19:22

Kirill Polishchuk

56.4k11 gold badges125 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kirill Polishchuk Over a year ago

@WillP., It is ignore-case option. Try use /i instead.

Will P. Over a year ago

I am only getting the first one returning using cyber-reality.com/regexy.html

Kirill Polishchuk Over a year ago

@WillP., Try this JavaScript tester: regexpal.com. It gives correct result.

Mike Samuel · Accepted Answer · 2012-02-08 19:25:03Z

0

If your WHERE clauses are free-form, /\(.+?\) (and|or)/i will spuriously match inside tokens like strings and comments.

Consider

SELECT resting_place FROM Nation
WHERE date_founded LIKE "Four score and seven years ago"
AND conceived IN (liberty)
ORDER BY the_people

The and in "Four score and seven years" is not a SQL keyword, but a single regular expression approach is not going to be able to distinguish keyword uses from non-keyword uses.

answered Feb 8, 2012 at 19:25

Mike Samuel

121k30 gold badges230 silver badges255 bronze badges

Comments

Has QUIT--Anony-Mousse · Accepted Answer · 2012-02-08 19:31:56Z

0

A more robust way would probably be to do a proper parser. This should be doable with a classic LL(1) parser. There are plenty of parser generators around. In fact, one can easily consider regular expressions to be a kind of parser generator, too.

Anyway, for this kind of tasks, a proper LL(1) parser is likely the better idea. It would also be able to support nested terms, if you want.

https://en.wikipedia.org/wiki/LL_parser

answered Feb 8, 2012 at 19:31

Has QUIT--Anony-Mousse

77.8k14 gold badges146 silver badges198 bronze badges

Collectives™ on Stack Overflow

Regular expression parsing sql filter

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related