sql - VB.Net Datatable Select MID函数

Question

我正在尝试从“CODE”遵循模式“Z##A”的数据表中提取所有行。我尝试了以下无济于事:

Dim floods() As DataRow = arqTable.Select("mid(code,1,1)='Z' and isnumeric(mid(code,2,2)) and mid(code,4,1)='A'")

返回错误

"The expression contains undefined function call mid()."

我可以使用FOR EACH 循环遍历行，但我很好奇是否有一种方法可以简单地使用数据表选择功能。

*编辑:顺便说一句，使用“像‘Z%A’这样的代码”是行不通的，因为我专门寻找 Z[number][number]A 而不是 Z[letter][letter]A .

Answer 1

这个有效

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 ")

如果您非常关心效率，请查看此快速示例，将其作为从性能角度来看的兴趣点。 (粘贴到控制台应用程序中)。

结果...

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

代码

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