If I've understood what you're asking for, you have a string containing an INSERT command, and you want to extract the values.
The best way to do that would be with a Regular Expression:
Regular Expression Language - Quick Reference | Microsoft Docs[^]
For example:
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 Usage:
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 Output:
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'
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|