Dies funktioniert
Dim r1() As DataRow = dt.Select("code LIKE 'Z%' and code LIKE '%A' and substring(code, 2, 1) >= '0' and substring(code, 2, 1) <= '9' and substring(code, 3, 1) >= '0' AND substring(code, 3, 1) <= '9' and len(code) = 4 ")
Wenn Sie sich über die Effizienz Gedanken machen, werfen Sie einen Blick auf dieses schnelle Beispiel aus performanceperspektive. (fügen Sie es in eine Konsolenanwendung ein).
Ergebnisse...
1000 DATENSÄTZE...
SELECT: 00:00:00.0050357 entspricht 141
VBLIKE: 00:00:00.0021198 entspricht 141 - %250.0
VBRAW: 00:00:00.0007444 entspricht 141 - Unendlich
CREGEX: 00:00:00.0014745 entspricht 141 - %500.0
10000 DATENSÄTZE...
SELECT: 00:00:00.0530854 entspricht 1430
VBLIKE: 00:00:00.0280535 entspricht 1430 - %189.3
VBRAW: 00:00:00.0067957 entspricht 1430 - %883.3
CREGEX: 00:00:00.0026389 entspricht 1430 - %2650.0
100000 DATENSÄTZE...
SELECT: 00:00:00.6141986 entspricht 13929
VBLIKE: 00:00:00.1773157 entspricht 13929 - %346.9
VBRAW: 00:00:00.0699633 entspricht 13929 - %889.9
CREGEX: 00:00:00.0271444 entspricht 13929 - %2274.1
1000000 DATENSÄTZE...
SELECT: 00:00:06.2316807 entspricht 138987
VBLIKE: 00:00:01.7882370 entspricht 138987 - %348.5
VBRAW: 00:00:00.7093068 entspricht 138987 - %878.8
CREGEX: 00:00:00.2714249 entspricht 138987 - %2299.3
Code
Imports System.Text.RegularExpressions
Module Module1
Public RegEx_Code As New Regex("^Z[0-9][0-9]A$", RegexOptions.Compiled)
Sub Main()
Dim trials() As Integer = {1000, 10000, 100000, 1000000} 'Datengrößen zum Testen
For Each recCnt As Integer In trials
'Testdaten aufbauen, die irgendwie ähnlich sind.
Dim dt As New DataTable
dt.Columns.Add("code", GetType(String))
For iQ As Integer = 0 To recCnt - 1
dt.Rows.Add(If(iQ Mod 4 = 0, "Z", "X") & Chr(Int(Rnd() * 15) + 48) & Chr(Int(Rnd() * 12) + 48) & If(iQ Mod 2 = 0, "A", "Y"))
Next
'Test SELECT
Dim sw1 As Stopwatch = Stopwatch.StartNew
Dim r1() As DataRow = dt.Select("code LIKE 'Z%' and code LIKE '%A' and substring(code, 2, 1) >= '0' and substring(code, 2, 1) <= '9' and substring(code, 3, 1) >= '0' AND substring(code, 3, 1) <= '9' and len(code) = 4 ")
sw1.Stop()
'Test VB integriertes LIKE
Dim sw2 As Stopwatch = Stopwatch.StartNew
Dim r2 As New List(Of DataRow)(recCnt \ 20)
Dim rInd2 As Integer = dt.Columns("code").Ordinal
For Each r As DataRow In dt.Rows
If CStr(r(rInd2)) Like "Z##A" Then
r2.Add(r)
End If
Next
r2.TrimExcess()
sw2.Stop()
Dim sw3 As Stopwatch = Stopwatch.StartNew
Dim r3 As New List(Of DataRow)(recCnt \ 20)
Dim rInd3 As Integer = dt.Columns("code").Ordinal
For Each r As DataRow In dt.Rows
Dim value As String = CStr(r(rInd3))
If value.Length = 4 AndAlso IsNumeric(value.Substring(1, 1)) AndAlso IsNumeric(value.Substring(2, 1)) AndAlso value.StartsWith("Z") AndAlso value.EndsWith("A") Then
r3.Add(r)
End If
Next
r3.TrimExcess()
sw3.Stop()
'Test kompilierte reguläre Ausdrücke.
Dim sw4 As Stopwatch = Stopwatch.StartNew
Dim r4 As New List(Of DataRow)(recCnt \ 20)
Dim rInd4 As Integer = dt.Columns("code").Ordinal
For Each r As DataRow In dt.Rows
If RegEx_Code.IsMatch(CStr(r(rInd4))) Then
r4.Add(r)
End If
Next
r4.TrimExcess()
sw4.Stop()
Console.WriteLine(recCnt & " DATENSÄTZE...")
Console.WriteLine("SELECT: " & sw1.Elapsed.ToString & " entspricht " & r1.Length)
Console.WriteLine("VBLIKE: " & sw2.Elapsed.ToString & " entspricht " & r2.Count & " - " & CDbl(sw1.ElapsedMilliseconds / sw2.ElapsedMilliseconds).ToString("%0.0"))
Console.WriteLine("VBRAW: " & sw3.Elapsed.ToString & " entspricht " & r3.Count & " - " & CDbl(sw1.ElapsedMilliseconds / sw3.ElapsedMilliseconds).ToString("%0.0"))
Console.WriteLine("CREGEX: " & sw4.Elapsed.ToString & " entspricht " & r3.Count & " - " & CDbl(sw1.ElapsedMilliseconds / sw4.ElapsedMilliseconds).ToString("%0.0"))
Console.WriteLine()
Next
Console.ReadLine()
End Sub
End Module
