正则表达式使模式匹配和数据提取变得更容易(David Banister)
来源:互联网 发布:北京博彦网鼎java面试 编辑:程序博客网 时间:2024/06/06 00:36
- 使用正则表达式进行高效的 SQL 查询
- SQL Server 2005 对正则表达式的支持
- 从 SQL Server 使用 .NET Regex 类
- 在数据库中有效地使用正则表达式
SQL Server 2005,.NET Framework
下载本文中所用的代码: Regex2007_02.exe (154 KB)
浏览在线代码
模式匹配
数据提取
模式存储
匹配
在匹配项中进行数据提取
总结
public static partial class UserDefinedFunctions
{
public static readonly RegexOptions Options =
RegexOptions.IgnorePatternWhitespace |
RegexOptions.Singleline;
[SqlFunction]
public static SqlBoolean RegexMatch(
SqlChars input, SqlString pattern)
{
Regex regex = new Regex( pattern.Value, Options );
return regex.IsMatch( new string( input.Value ) );
}
}
select dbo.RegexMatch( N'123-45-6789', N'^/d{3}-/d{2}-/d{4}$' )
select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = N'PROCEDURE'
and dbo.RegexMatch( ROUTINE_NAME,
N'^usp_(Insert|Update|Delete|Select)([A-Z][a-z]+)+$' ) = 0
select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = N'PROCEDURE'
and ( LEN( ROUTINE_NAME ) < 11
or LEFT( ROUTINE_NAME, 4 ) <> N'usp_'
or SUBSTRING( ROUTINE_NAME, 5, 6 ) not in
( N'Insert', N'Update', N'Delete', N'Select' ) )
CREATE TABLE [Account]
(
[AccountNumber] nvarchar(20) CHECK (dbo.RegexMatch(
[AccountNumber], '^[A-Z]{3,5}/d{5}-/d{3}$' ) = 1),
[PhoneNumber] nchar(13) CHECK (dbo.RegexMatch(
[PhoneNumber], '^/(/d{3}/)/d{3}-/d{4}$' ) = 1),
[ZipCode] nvarchar(10) CHECK (dbo.RegexMatch(
[ZipCode], '^/d{5}(/-/d{4})?$' ) = 1)
)
[SqlFunction]
public static SqlChars RegexGroup(
SqlChars input, SqlString pattern, SqlString name )
{
Regex regex = new Regex( pattern.Value, Options );
Match match = regex.Match( new string( input.Value ) );
return match.Success ?
new SqlChars( match.Groups[name.Value].Value ) : SqlChars.Null;
}
select distinct dbo.RegexGroup( [Url],
N'https?://(?<server>([/w-]+/.)*[/w-]+)', N'server' )
from [UrlTable]
CREATE TABLE [Email]
(
[Address] nvarchar(max),
[Mailbox] as dbo.RegexGroup( [Address],
N'(?<mailbox>[^@]*)@', N'mailbox' ),
[Domain] as dbo.RegexGroup( [Address], N'@(?<domain>.*)', N'domain' )
internal class MatchNode
{
private int _index;
public int Index { get{ return _index; } }
private string _value;
public string Value { get { return _value; } }
public MatchNode( int index, string value )
{
_index = index;
_value = value;
}
}
internal class MatchIterator : IEnumerable
{
private Regex _regex;
private string _input;
public MatchIterator( string input, string pattern )
{
_regex = new Regex( pattern, UserDefinedFunctions.Options );
_input = input;
}
public IEnumerator GetEnumerator()
{
int index = 0;
Match current = null;
do
{
current = (current == null) ?
_regex.Match( _input ) : current.NextMatch( );
if (current.Success)
{
yield return new MatchNode( ++index, current.Value );
}
}
while (current.Success);
}
}
[SqlFunction( FillRowMethodName = "FillMatchRow",
TableDefinition = "[Index] int,[Text] nvarchar(max)" )]
public static IEnumerable RegexMatches(SqlChars input, SqlString pattern)
{
return new MatchIterator( new string( input.Value ), pattern.Value );
}
[SuppressMessage( "Microsoft.Design", "CA1021:AvoidOutParameters" )]
public static void FillMatchRow( object data,
out SqlInt32 index, out SqlChars text )
{
MatchNode node = (MatchNode)data;
index = new SqlInt32( node.Index );
text = new SqlChars( node.Value.ToCharArray( ) );
}
declare @text nvarchar(max), @pattern nvarchar(max)
select
@text = N'Here are four words.',
@pattern = '/w+'
select count(distinct [Text])
from dbo.RegexMatches( @text, @pattern )
declare @pattern nvarchar(max), @list nvarchar(max)
select @pattern = N'[^,]+', @list = N'2,4,6'
select d.* from [Data] d
inner join dbo.RegexMatches( @list, @pattern ) re
on d.[ID] = re.[Text]
internal class GroupNode
{
private int _index;
public int Index { get { return _index; } }
private string _name;
public string Name { get { return _name; } }
private string _value;
public string Value { get { return _value; } }
public GroupNode( int index, string group, string value )
{
_index = index;
_name = group;
_value = value;
}
}
internal class GroupIterator : IEnumerable
{
private Regex _regex;
private string _input;
public GroupIterator( string input, string pattern )
{
_regex = new Regex( pattern, UserDefinedFunctions.Options );
_input = input;
}
public IEnumerator GetEnumerator()
{
int index = 0;
Match current = null;
string[] names = _regex.GetGroupNames();
do
{
index++;
current = (current == null) ?
_regex.Match( _input ) : current.NextMatch( );
if (current.Success)
{
foreach(string name in names)
{
Group group = current.Groups[name];
if (group.Success)
{
yield return new GroupNode(
index, name, group.Value );
}
}
}
}
while(current.Success);
}
}
[SqlFunction( FillRowMethodName = "FillGroupRow", TableDefinition =
"[Index] int,[Group] nvarchar(max),[Text] nvarchar(max)" )]
public static IEnumerable
RegexGroups( SqlChars input, SqlString pattern )
{
return new GroupIterator( new string( input.Value ), pattern.Value );
}
[SuppressMessage( "Microsoft.Design", "CA1021:AvoidOutParameters" )]
public static void FillGroupRow( object data,
out SqlInt32 index, out SqlChars group, out SqlChars text )
{
GroupNode node = (GroupNode)data;
index = new SqlInt32( node.Index );
group = new SqlChars( node.Name.ToCharArray( ) );
text = new SqlChars( node.Value.ToCharArray( ) );
}
2309478,Janet Leverling,J
2039748,Nancy Davolio,N
0798124,Andrew Fuller,M
4027392,Robert King,L
(?<CustomerNumber>/d{7}),(?<CustomerName>[^,]*),(?<CustomerType>[A-Z])/r?/n
create proc ImportCustomers
(
@file nvarchar(max)
)
as
declare @pattern nvarchar(max)
set @pattern = N'(?<CustomerNumber>/d{7}),
(?<CustomerName>[^,]*),(?<CustomerType>[A-Z])/r?/n'
insert [Customer]
(
[CustomerNumber],
[CustomerName],
[CustomerType]
)
select
f.[CustomerNumber],
f.[CustomerName],
f.[CustomerType]
from dbo.RegExGroups( @file, @pattern ) regex
pivot
(
max([Text])
for [Group]
in ( [CustomerNumber], [CustomerName], [CustomerType] )
) as f
- 正则表达式使模式匹配和数据提取变得更容易(David Banister)
- SQL Server 2005正则表达式使模式匹配和数据提取变得更容易~
- QRegExp一些注意事项(正则表达式和模式匹配)
- 正则表达式和模式匹配简介
- 正则表达式和模式匹配简介(二)
- 正则表达式和模式匹配简介(三)
- scala模式匹配和正则表达式
- mysql模式匹配和正则表达式
- Scala的模式匹配和正则表达式
- 正则表达式-匹配模式
- JavaServer Faces 技术使Web应用程序开发变得更容易
- Python 正则表达式(模式匹配)
- 正则表达式(五)------ 匹配模式
- Python 正则表达式(模式匹配)
- 正则表达式(候选模式匹配)
- Python 正则表达式(模式匹配)
- Python 正则表达式(模式匹配)
- 常用正则表达式匹配模式(java)
- 求教各位大侠,在数据库连接的时候,出现error: 26 - 定位指定的服务器/实例时出错,紧急救助!
- FLEX实例:几款滤镜效果实例.
- C++ styles
- MATLAB生成COM调用异常—没有找到mclmcrrt73.dll
- 大家好
- 正则表达式使模式匹配和数据提取变得更容易(David Banister)
- Java培训笔记四(开始正式培训)
- "/n"和"/012"还有"/015/012" 是什么
- Struts2工作原理(转)
- Java培训笔记五
- power law,small world,bow-tie理论
- Java培训笔记六
- Android手机分辨率自适应和机型汇总
- 一个检验身份证合法性用PLSQL实现的方法