记录一下sqlserver使用正则匹配字符串的方法(替换就不写了,网上比较多)
一:如果只是查找表里有哪些符合条件的记录,只需要创建函数:
USE [TableName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[RegexMatch]
(
@pattern VARCHAR(2000),
@matchstring VARCHAR(8000)--Varchar(8000) got SQL Server 2000
)
RETURNS INT
/* The RegexMatch returns True or False, indicating if the regular expression matches (part of) the string. (It returns null if there is an error).
When using this for validating user input, you'll normally want to check if the entire string matches the regular expression. To do so, put a caret at the start of the regex, and a dollar at the end, to anchor the regex at the start and end of the subject string.
*/
AS BEGIN
DECLARE @objRegexExp INT,
@objErrorObject INT,
@strErrorMessage VARCHAR(255),
@hr INT,
@match BIT
SELECT @strErrorMessage = 'creating a regex object'
EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
--Specifying a case-insensitive match
IF @hr = 0
EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
--Doing a Test'
IF @hr = 0
EXEC @hr= sp_OAMethod @objRegexExp, 'Test', @match OUT, @matchstring
IF @hr <> 0
BEGIN
RETURN NULL
END
EXEC sp_OADestroy @objRegexExp
RETURN @match
END
用法:
select dbo.RegexMatch('http://(.+?)\.jpg',intro) ,intro from TableName
就是查询表里 有哪些是包含 ‘http://’开头,'.jpg'结尾的数据,有的则查询结果为1
二:如果以上不能满足要求,还需要查出符合条件的具体是哪些字符串:
首先需要把以下代码封装成dll,假设取名为SQLCLR_RegExp.dll(C#为例):
using System;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
public partial class RegExp
{
[Microsoft.SqlServer.Server.SqlFunction]
public static string RegExp_Like(SqlString input, SqlString pattern)
{
if (input.IsNull || pattern.IsNull) return null;
//Regex.Match表示匹配第一个符合条件的;
//Regex.Matches表示匹配所有符合条件的;
var matches= Regex.Matches(input.Value, pattern.Value);
var result = "";
//如果一条记录有多个字符串匹配,用符号隔开
foreach (Match match in matches)
{
result += match.Value + "|";
}
result = result.TrimEnd('|');
return result;
}
};
然后在SQLServer中添加对此DLL的引用,并创建匹配函数:
go
exec sp_configure 'clr enabled' , '1'
go
reconfigure ;
exec sp_configure 'show advanced options' , '1' ;
go
reconfigure ;
ALTER DATABASE TableName SET TRUSTWORTHY On;
create ASSEMBLY SqlClr_RegEx FROM 'E:/你生成dll的路径/SQLCLR_RegExp.dll' WITH PERMISSION_SET = UnSAFE;
--
go
CREATE FUNCTION dbo.RegExp_Like
(
@input nvarchar ( max ),
@pattern nvarchar ( 4000)
)
RETURNS nvarchar ( max )
AS EXTERNAL NAME SqlClr_RegEx.RegExp.RegExp_Like
用法:
select dbo.RegExp_Like(intro,'http://(.+?)\.jpg'), intro from TableName
这样就找到了每条记录符合条件的字符串分别是哪些,并且多个的话以“|”隔开了,方便处理