Parse an Update Statement to get Field/Value pairs

Results 1 to 2 of 2

Thread: Parse an Update Statement to get Field/Value pairs

  1. #1
    Join Date
    Dec 1969

    Default Parse an Update Statement to get Field/Value pairs

    I have a need to parse an Update statement and get the field names and values into arrays (one single dim array for fields, one for values). The reason is in a certain scenario, i need to know exactly what was changed.<BR>No big deal so far:<BR><BR>(the variable sql is being passed in)<BR><BR> &#039;Now get our fields/values that were modified prepared<BR> Dim StringArray<BR> Dim TempArray<BR> <BR> StringArray = Split(sql, " Set ", , vbTextCompare)<BR> StringArray = Split(CStr(StringArray(1)), " Where ", , vbTextCompare)<BR> <BR> &#039;We now have a name=value, name=value string<BR> StringArray = Split(CStr(StringArray(0)), ",")<BR> <BR> &#039;We now have an array of name=value strings<BR> Dim i As Long<BR> Dim FieldArray<BR> Dim ValueArray<BR> <BR> For i = 0 To UBound(StringArray)<BR> TempArray = Split(CStr(StringArray(i)), "=")<BR> AddToAnyArray FieldArray, Trim(TempArray(0))<BR> AddToAnyArray ValueArray, Trim(TempArray(1))<BR> Next<BR><BR>Works just fine if none of the field names or their values have "=", ",", " Set " or " Where " in them! Any good ideas on how to deal with this?

  2. #2
    Join Date
    Dec 1969

    Default SPLIT was never made...

    ...for parsing general text like that.<BR><BR>For that matter, there isn&#039;t really anything in VBScript that was made for true parsing.<BR><BR>You might have some luck with regular expressions. Hmmm...<BR><BR>/^update w+ set/i <BR> -- replace that with nothing to get rid of stuff on front end<BR><BR>/^(w+)s*=s*&#039;(([^&#039;]&#124&#039;&#039;)*)&#039;s*/<BR> -- look for that pattern, to get <BR> fldname = &#039;it&#039;&#039;s like this&#039;<BR> -- but if that isn&#039;t found, then fall back to<BR>/^(w+)s*=s*([d.+-]+)s*/<BR> -- to find <BR> fldname = -9.717<BR><BR>When you find either of those, then you get remember it and strip it from the string. If you find a comma as next character, you repeat.<BR><BR>No, please don&#039;t ask me how to make it actually all work. Go ask "Digory" in the RegularExpressions forum.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts