How To Define A Regular Expression With Multiple Or Operators Where Each Term Includes A Space Prefix And Suffix?

I am preparing for a data extraction task. I need to remove a set of terms; none, some or all may be present in each source record string. There are over 100,000 target records. I

Solution 1:

Try this:

(?: (?:andor|and|a o|company|co|c o|dba|d b a))+(?!\S)/i

Like @mathematical.coffee, I started by factoring out the leading space and replacing the trailing space with a lookahead--in this case, a negative lookahead for a non-whitespace character. This way it will work even if the token is the last one in the string and not followed by a space. But the most important change is replacing two or more matches at a time whenever possible.

Solution 2:

This isn't a SQL Server problem. This is a general RegEx problem - and not just the one included in the VBScript engine which you are accessing through COM. The problem is that the matches actually overlap between old and new prefix spaces.

I tried your example in http://www.regextester.com/ and it does the same thing.

The " and or " which is the first thing not replaced is actually made up of the space from the first " and " which was replaced by a space and then the remaining text.

I would look at using word boundary replacement instead: Regex match and replace word delimited by certain characters

Solution 3:

I'd recommend this regex:

( (and(?:or)?|a o|company|c ?o|d ?b ?a)(?= ))

First of all, I put the prefix/suffix spaces outside your OR brackets (efficiency):

( (and(?:or)?|a o|company|c ?o|d ?b ?a) )

However when you use this regex your matches overlap. For example and and or matches first the and, but then the remaining string is and or which doesn't have the preceding space.

So to get around this, I changed the last space to a positive lookahead. It says "make sure this pattern is followed by a space", but doesn't match the space itself.

So when going through and and or it matches and and leaves and or, which also matches the pattern. It more-or-less removes the problem of overlapping matches. This won't match one of your words if it occurs at the end of a string, but your original regex didn't anyway.

You can see it in action at the regexr site. Note that if you replace each match with a space you'll end up with way too many spaces:

MASHABLE LTD  THE INFORMATION EXPERTS         COPYRIGHT

But you'd have that problem with your original regex anyway. If you remove the matches entirely you'll get:

MASHABLE LTD THE INFORMATION EXPERTS COPYRIGHT