Function TestStr(ByVal Test_str As String, ByVal match_str As String) As Boolean
'引用:Microsoft VBScript Regular Expressions 5.5
'功能:是否匹配正则表达式
'参数:Test_str--测试字符串,match_str--正则表达式
Dim re As New regexp
re.Pattern = match_str
re.IgnoreCase = True
re.Global = True
TestStr = re.Test(Test_str)
Set re = Nothing
End Function
Function ReplaceMatch(ByVal str As String, ByVal match_str As String, ByVal Rematch_str As String) As String
'引用:Microsoft VBScript Regular Expressions 5.5
'功能:替换字符串
'参数:Test_str--测试字符串,match_str--源正则表达式,Rematch_str--替换的正则表达式
Dim re As New regexp
re.Pattern = match_str
re.IgnoreCase = True
re.Global = True
ReplaceMatch = re.Replace(str, Rematch_str)
Set re = Nothing
End Function
有了这两个函数,我们就可以在查询中引用函数来找到G后面的数字,我们可以这样写查询:
Select itemdetail.ITEM, itemdetail.desc, IIf(teststr([desc],"G\d+"),ReplaceMatch([desc],"(^.*G)(\d+)(.*$)","$2"),"") AS G后的数字
FROM itemdetail;