Regex to replace BETWEEN SQL operator with AND

Question

I have SQL Where clause to run on DataView, but C# DataView does not accept BETWEEN operator. I need to convert any BETWEEN operator in query string to And, example:

Before: Year BETWEEN 2010 and 2014

After: Year >= 2010 and Year <= 2014

I tried this pattern

$.+ BETWEEN .+ AND .+/

But it didn't work, also not replacing anything so far

It's a string to manipulate with regex replace. I gave example in the post. — Green Train
– Green Train, Commented Aug 16, 2016 at 12:04
woah there - you need to be really careful with regex; BETWEEN .+ AND .+ could replace some very different scenarios, including the AND in a WHERE clause — Marc Gravell
– Marc Gravell, Commented Aug 16, 2016 at 12:10
"I tried this pattern" - showing us just the match pattern (which doesn't actually have any replacement groups to start with) isn't really enough - we'd need to see the entire usage — Marc Gravell
– Marc Gravell, Commented Aug 16, 2016 at 12:18

Marc Gravell · Accepted Answer · 2016-08-16 12:14:54Z

1

This works, but I think you would do better to write a query that matches the required syntax...

var sql = "blah Year BETWEEN 2010 and 2014 blah";
var munged = Regex.Replace(sql,
    @"\s([A-Za-z0-9_.]+)\s+BETWEEN\s+([0-9]+)\s+and\s+([0-9]+)\s",
    " $1 >= $2 and $1 <= $3 ", RegexOptions.IgnoreCase);

Note that I'm limiting the handling here to integers; expanding it to cope with strings is much more complex.

answered Aug 16, 2016 at 12:14

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

Collectives™ on Stack Overflow

Regex to replace BETWEEN SQL operator with AND

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related