This one works
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 ")
If your at all concerned about efficiency check out this quick example as a point of interest from a performance perspective. (paste into a console application).
Results...
1000 RECORDS...
SELECT: 00:00:00.0050357 matches 141
VBLIKE: 00:00:00.0021198 matches 141 - %250.0
VBRAW: 00:00:00.0007444 matches 141 - Infinity
CREGEX: 00:00:00.0014745 matches 141 - %500.0
10000 RECORDS...
SELECT: 00:00:00.0530854 matches 1430
VBLIKE: 00:00:00.0280535 matches 1430 - %189.3
VBRAW: 00:00:00.0067957 matches 1430 - %883.3
CREGEX: 00:00:00.0026389 matches 1430 - %2650.0
100000 RECORDS...
SELECT: 00:00:00.6141986 matches 13929
VBLIKE: 00:00:00.1773157 matches 13929 - %346.9
VBRAW: 00:00:00.0699633 matches 13929 - %889.9
CREGEX: 00:00:00.0271444 matches 13929 - %2274.1
1000000 RECORDS...
SELECT: 00:00:06.2316807 matches 138987
VBLIKE: 00:00:01.7882370 matches 138987 - %348.5
VBRAW: 00:00:00.7093068 matches 138987 - %878.8
CREGEX: 00:00:00.2714249 matches 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} 'data sizes to test
For Each recCnt As Integer In trials
'build test data that is sort of similar.
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 built in 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 Compiled Regular Expression.
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 & " RECORDS...")
Console.WriteLine("SELECT: " & sw1.Elapsed.ToString & " matches " & r1.Length)
Console.WriteLine("VBLIKE: " & sw2.Elapsed.ToString & " matches " & r2.Count & " - " & CDbl(sw1.ElapsedMilliseconds / sw2.ElapsedMilliseconds).ToString("%0.0"))
Console.WriteLine("VBRAW: " & sw3.Elapsed.ToString & " matches " & r3.Count & " - " & CDbl(sw1.ElapsedMilliseconds / sw3.ElapsedMilliseconds).ToString("%0.0"))
Console.WriteLine("CREGEX: " & sw4.Elapsed.ToString & " matches " & r3.Count & " - " & CDbl(sw1.ElapsedMilliseconds / sw4.ElapsedMilliseconds).ToString("%0.0"))
Console.WriteLine()
Next
Console.ReadLine()
End Sub
End Module
