Visual Basic

dcof wrote:

Can you show me the query you are referring to?

Public Class StatementInfo
    Private ReadOnly Shared Parser As New Regex(
        "INSERT\s+(INTO\s+)?(?<table>[^(]+)\s*\((?<col>[^,)]+)(,\s*(?<col>[^,)]+))*\)\s*VALUES\s*\((?<val>[^,)]+)(,\s*(?<val>[^,)]+))*\)", 
        RegexOptions.IgnoreCase Or RegexOptions.ExplicitCapture)
    
    Public Shared Function Parse(ByVal input As String) As StatementInfo
        Dim match As Match = Parser.Match(input)
        If Not match.Success Then Return Nothing
        
        Dim tableName As String = match.Groups("table").Value
        Dim columnNames As List(Of String) = match.Groups("col").Captures.Cast(Of Capture)().Select(Function (c) c.Value.Trim()).ToList()
        Dim values As List(Of String) = match.Groups("val").Captures.Cast(Of Capture)().Select(Function (c) c.Value.Trim()).ToList()
        If values.Count <> columnNames.Count Then Throw New ArgumentException("Mis-matched columns and values")
        
        Return New StatementInfo(tableName, columnNames, values)
    End Function
    
    Private Sub New(ByVal tableName As String, ByVal columnNames As List(Of String), ByVal values As List(Of String))
        Me.TableName = tableName
        Me.Values = columnNames _
            .Zip(values, Function (c, v) New With { .Key = c, .Value = v }) _
            .ToDictionary(Function (p) p.Key, Function (p) p.Value, StringComparer.OrdinalIgnoreCase)
    End Sub
    
    Public ReadOnly Property TableName As String
    Public ReadOnly Property Values As IReadOnlyDictionary(Of String, String)
End Class

Dim line As String = "INSERT INTO tblInvoiceData(SchNum,InvNum,InvDate,ItemNum, Quantity,UnitPrice,Credit,Amount,VendorId,CatId,Boarddate,RecordId,Imported )VALUES ('896', '558957', '5/2/2016', '3150', '1', '018.99', '', '18.99', '8277', '17', '12/4/2017', '1789655895720160502315018.99', 'Y')"
Dim statement As StatementInfo = StatementInfo.Parse(line)
If statement IsNot Nothing Then
    Console.WriteLine("Table: {0}", statement.TableName)
    For Each pair As KeyValuePair(Of String, String) In statement.Values
        Console.WriteLine("{0} = {1}", pair.Key, pair.Value)
    Next
End If

Table: tblInvoiceData
SchNum = '896'
InvNum = '558957'
InvDate = '5/2/2016'
ItemNum = '3150'
Quantity = '1'
UnitPrice = '018.99'
Credit = ''
Amount = '18.99'
VendorId = '8277'
CatId = '17'
Boarddate = '12/4/2017'
RecordId = '1789655895720160502315018.99'
Imported = 'Y'