T-SQL中的正则表达式 [转载]

 

T-SQL中的正则表达式 [转载]

本文来自Cory Koski
“我最近遇到一个问题，就是试图在数据库域中搜索一个正则表达式。还没有一个版本的SQLServer内部支持正则表达式，但我发现了一个将正则表达式的所有优点添加到你的T_SQL应用的方法。为了更容易的使用正则表达式，我们可以使用自定义函数（User Defined Function, UDF）来帮助我们并使工作简洁。”

在这个解决方案中，我们需要SQL Server 2000或更高。我们还需要确定机器中有VBScript.RegExp类库，这随大多数Windows 2000 servers中的Windows Scripting包配有。若你正在使用一个更早版本的Windows，你必须为你的操作系统下载最新版的Windows Scripting。
自定义函数
下面是我的自定义函数，可用来在源字符串中搜索一个正则模式表达式。
CREATE FUNCTION dbo.find_regular_expression
(
@source varchar(5000),
@regexp varchar(1000),
@ignorecase bit = 0
)
RETURNS bit
AS
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer
DECLARE @objMatches integer
DECLARE @objMatch integer
DECLARE @count integer
DECLARE @results bit

EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
RETURN @results
END

将这个UDF保存到你的数据库中，并确定有授权来运行它。当然，你也得确保运行它的人有运行sp_OAxxxxx类扩展存储过程的权限。
这个函数已确保正常运行，并且即便是和COM对象一起使用，也还是挺快的。

举例
使用正则表达式的一个地方就是测试特殊字符。我们不搜索所有的特殊字符，而是查找正常字符的匹配项，例如字母和空格。我们看看它的运行。
DECLARE @intLength AS INTEGER
DECLARE @vchRegularExpression AS VARCHAR(50)
DECLARE @vchSourceString as VARCHAR(50)
DECLARE @vchSourceString2 as VARCHAR(50)
DECLARE @bitHasNoSpecialCharacters as BIT

-- 初始化变量
SET @vchSourceString = 'Test one This is a test!!'
SET @vchSourceString2 = 'Test two This is a test'

-- 我们的正则表达式应该类似于
-- [a-zA-Z ]{}
-- 如： [a-zA-Z ]{10} ... 一个十字符的字符串

-- 获得字符串长度
SET @intLength = LEN(@vchSourceString)

-- 设置完整的正则表达式
SET @vchRegularExpression = '[a-zA-Z ]{' + 
CAST(@intLength as varchar) + '}'

-- 是否有任何特殊字符
SET @bitHasNoSpecialCharacters = dbo.find_regular_expression(
@vchSourceString, @vchRegularExpression,0)

PRINT @vchSourceString
IF @bitHasNoSpecialCharacters = 1 BEGIN
PRINT 'No special characters.'
END ELSE BEGIN
PRINT 'Special characters found.'
END

PRINT '---'

-- 获得字符串长度
SET @intLength = LEN(@vchSourceString2)

-- 设置完整的正则表达式
SET @vchRegularExpression = '[a-zA-Z ]{' + 
CAST(@intLength as varchar) + '}'

-- 是否有任何特殊字符
SET @bitHasNoSpecialCharacters = dbo.find_regular_expression(
@vchSourceString2, @vchRegularExpression,0)

PRINT @vchSourceString2
IF @bitHasNoSpecialCharacters = 1 BEGIN
PRINT 'No special characters.'
END ELSE BEGIN
PRINT 'Special characters found.'
END

GO
The results for this example would be: 
本例的结果应该是：
Test one This is a test!!
Special characters found.
---
Test two This is a test
No special characters.

结论：
正如你所见，这是一个简单技巧，在特定的场合得到了非常有用的结果。你作为一个T_SQL开发人员，可以在正则表达式库VBScript.RegExp中使用和扩展这个技巧。

kangasp_自由舰_河北 | 高级车友

---

[1楼]：
此有三个关键的存储过程：
sp_OACreate
在 Microsoft® SQL Server™ 实例上创建 OLE 对象实例。


语法
sp_OACreate progid, | clsid,
    objecttoken OUTPUT
    [ , context ]

参数
progid

是要创建的 OLE 对象的程序标识符 (ProgID)。此字符串描述该 OLE 对象的类，其形式如下：

'OLEComponent.Object'

OLEComponent 是 OLE 自动化服务器的组件名称，Object 是 OLE 对象名。指定的 OLE 对象必须有效并且必须支持 IDispatch 接口。

例如，SQLDMO.SQLServer 是 SQL-DMO SQLServer 对象的 ProgID。SQL-DMO 的组件名称为 SQLDMO，SQLServer 对象是有效的，并且同所有 SQL-DMO 对象一样，SQLServer 对象支持 Idispatch。

说明  此存储过程的参数按位置指定，而不是按名称指定。

返回代码值
0（成功）或非零数字（失败），是由 OLE 自动化对象返回的 HRESULT 的整数值。

注释
已创建的 OLE 对象在 Transact-SQL 语句批处理结束时会自动释放。

权限
只有 sysadmin 固定服务器角色的成员才能执行 sp_OACreate。


2005-07-06 13:10 | 修改于：2005-07-06 13:11
楼主 kangasp_ 自由舰_ 河北 | 高级车友

---

[2楼]：
sp_OASetProperty
将 OLE 对象的属性设置为新值。

语法
sp_OASetProperty objecttoken,
    propertyname,
    newvalue
    [ , index... ]

参数
objecttoken

是先前用 sp_OACreate 创建的 OLE 对象令牌。

propertyname

是要设置新值的 OLE 对象的属性名。

newvalue

是属性的新值，并且必须是适当数据类型的值。

index

是索引参数。如果指定，那么它必须是适当数据类型的值。

一些属性带有参数。这些属性称为索引化属性，其参数被称为索引参数。一个属性可有多个索引参数。

说明  此存储过程的参数按位置指定，而不是按名称。

返回代码值
0（成功）或非零数字（失败），是由 OLE 自动化对象返回的 HRESULT 的整数值。
2005-07-06 13:11
楼主 kangasp_ 自由舰_ 河北 | 高级车友

---

[3楼]：
sp_OAMethod
调用 OLE 对象的方法。

语法
sp_OAMethod objecttoken,
    methodname
    [, returnvalue OUTPUT]
    [ , [ @parametername = ] parameter [ OUTPUT ]
    [...n]]

参数
objecttoken

是先前用 sp_OACreate 创建的 OLE 对象令牌。

methodname

是要调用的 OLE 对象的方法名。

returnvalue OUTPUT

是该 OLE 对象的方法的返回值。如果指定，那么它必须是适当数据类型的局部变量。

如果方法返回单个值，那么或者为 returnvalue 指定一个局部变量，以便在该局部变量中返回方法的返回值，或者不指定 returnvalue，以单列、单行结果集的形式将方法返回值返回给客户端。

如果方法返回值是 OLE 对象，那么 returnvalue 必须是数据类型为 int 的局部变量。对象令牌将保存在该局部变量中，并且可由其它 OLE 自动化存储过程使用。

当方法返回值是数组时，如果指定了 returnvalue，那么该值将被设置为 NULL。

在下列情况下会出现错误： 

指定了 returnvalue，但是方法并不返回值。

方法返回二维以上的数组。

方法以输出参数的形式返回数组。 
[@parametername =] parameter [OUTPUT]

是方法参数。如果指定，那么 parameter 必须是适当数据类型的值。

若要获取输出参数的返回值，parameter 必须是适当数据类型的局部变量，并且必须指定 OUTPUT。如果指定的是常量参数，或未指定 OUTPUT，那么输出参数的任何返回值均被忽略。

如果指定，则 parametername 必须是 Microsoft® Visual Basic® 命名参数的名称。要注意 @parametername 不并是 Transact-SQL 局部变量。将去掉符号 (@)，并将 parametername 作为参数名传递给 OLE 对象。指定所有位置参数后才能指定命名参数。

n

是表示可以指定多个参数的占位符。

说明  @parametername 可以是命名参数，因为它是指定方法的一部分，并将直接传递给对象。此存储过程的其它参数是按位置指定的，而不是按名称。

返回代码值
0（成功）或非零数字（失败），是由 OLE 自动化对象返回的 HRESULT 的整数值。