我的问题：如何修改正则表达式以包含OR分隔列表中的每个术语？

正则表达式

' and | and or | a o | company | co | c o | dba | d b a '

期望的行为

用单个空格替换每个找到的术语(包括前缀和后缀空格).

实际行为

找到的每个“偶数”(相对于“奇数”)术语都用一个空格替换(包括前缀和后缀空格).

例

源字符串

'   MASHABLE LTD DBA THE INFORMATION EXPERTS and and or a o company co c o dba d b a COPYRIGHT  '

结果字符串(所需行为)

'   MASHABLE LTD THE INFORMATION EXPERTS COPYRIGHT  '

结果字符串(实际行为)

'   MASHABLE LTD THE INFORMATION EXPERTS and or company c o d b a COPYRIGHT  '

环境

sql Server 2005

用户定义函数regexReplace依赖于VBScript.RegExp(代码末尾可用的代码)

码

set nocount on

declare @source [varchar](800)
declare @regexp [varchar](400)
declare @replace [char](1)
declare @globalReplace [bit]
declare @ignoreCase [bit]
declare @result [varchar](800)

set @globalReplace = 1
set @ignoreCase = 1

SET @source = '   MASHABLE LTD DBA THE INFORMATION EXPERTS and and or a o company co c o dba d b a COPYRIGHT  '
set @regexp = ' and | and or | a o | company | co | c o | dba | d b a '
set @replace = ' '

select @result = master.dbo.regexReplace(@source,@regexp,@replace,@globalReplace,@ignoreCase)

print @result

…产生结果：

MASHABLE LTD THE INFORMATION EXPERTS and or company c o d b a COPYRIGHT

* dbo.regexReplace用户定义的函数定义*

CREATE FUNCTION [dbo].[regexReplace]
    (
    @source varchar(5000),@regexp varchar(1000),@replace varchar(1000),@globalReplace bit = 0,@ignoreCase bit = 0
    )
    RETURNS varchar(1000) AS
        BEGIN
            DECLARE @hr integer
            DECLARE @objRegExp integer
            DECLARE @result varchar(5000)

            EXECUTE @hr = sp_OACreate 'VBScript.RegExp',@objRegExp OUTPUT
            IF @hr <> 0 
                BEGIN
                    EXEC @hr = sp_OADestroy @objRegExp
                    RETURN NULL
                END
            EXECUTE @hr = sp_OASetProperty @objRegExp,'Pattern',@regexp
            IF @hr <> 0 
                BEGIN
                    EXEC @hr = sp_OADestroy @objRegExp
                    RETURN NULL
                END
            EXECUTE @hr = sp_OASetProperty @objRegExp,'Global',@globalReplace
            IF @hr <> 0 
                BEGIN
                    EXEC @hr = sp_OADestroy @objRegExp
                    RETURN NULL
                END
            EXECUTE @hr = sp_OASetProperty @objRegExp,'IgnoreCase',@ignoreCase
            IF @hr <> 0 
                BEGIN
                    EXEC @hr = sp_OADestroy @objRegExp
                    RETURN NULL
                END

            EXECUTE @hr = sp_OAMethod @objRegExp,'Replace',@result OUTPUT,@source,@replace
            IF @hr <> 0 
                BEGIN
                    EXEC @hr = sp_OADestroy @objRegExp
                    RETURN NULL
                END

            EXECUTE @hr = sp_OADestroy @objRegExp
                IF @hr <> 0 
                    BEGIN
                        RETURN NULL
                    END

            RETURN @result
        END