excel vba - 转换sql

Question

有没有人有幸使用 vba 宏来将此输入转换

update my_table 
    set time = sysdate, 
    randfield1 = 'FAKE', 
    randfield5 = 'ME', 
    the_field8 = 'test' 
    where my_key = '84' 
    ;

为此输出？

select count(*) from my_table
where (randfield1 <> 'FAKE'
or randfield5 <> 'ME'
or the_field8 <> 'TEST')
and my_key = '84';

更新（使用Remou的答案时会发生什么）：

输入（我在第一张纸的单元格A1中放置的内容）-

update my_table  
    set time = sysdate,  
    randfield1 = 'FAKE',  
    randfield5 = 'ME',  
    the_field8 = 'test'  
    where my_key = '84'  
    ; 

输出（运行宏后在第二张纸的a1中生成的内容）-

SELECT Count(*) FROM  my_table
WHERE ()
)
)
)
)
)
)
)
randfield1 <> 'FAKE'
OR )
)
)
)
randfield5 <> 'ME'
OR )
)
)
)
the_field8 <> 'test')
)
)
)
)
AND my_key = '84'
;

Answer 1

我仍然不确定你想要什么，但无论如何：

Dim r As Range
Dim cl As Range
Dim s As String
Dim c As String
Dim arys As Variant
Dim i As Long, j As Long

''Assuming an existing worksheet
Set r = Sheet1.UsedRange
j = Sheet2.UsedRange.Rows.Count

For Each cl In r.Cells
    c = cl.Value

    ''Fake spaces
    Do While InStr(c, Chr(160)) > 0
        c = Replace(c, Chr(160), "")
    Loop

    ''Real spaces
    c = Trim(c)

    If c = ";" Then

        arys = Split(s, vbCrLf)

        For i = 0 To UBound(arys)
            Sheet2.Cells(j, 1) = arys(i)
            j = j + 1
        Next

        ''Layout
        j = j + 2

    ElseIf UCase(c) Like "UPDATE*" Then

        s = "SELECT Count(*) FROM " & Replace(c, "update", "", , , vbTextCompare)
        s = s & vbCrLf & "WHERE ("

    ElseIf UCase(c) Like "WHERE*" Then
        s = s & Replace(c, "where", "AND", , , vbTextCompare)
        s = s & vbCrLf & ";"

    ElseIf Left(UCase(c), 3) <> "SET" Then
        c = Replace(c, "=", "<>")

        If Right(c, 1) = "," Then
            s = s & Left(c, Len(c) - 1) & vbCrLf & "OR "
        Else
            s = s & c & ")" & vbCrLf
        End If

    End If
Next

Answer 2

好吧，这至少对您的示例输入有用......

Sub NotExtensibleInTheLeast()

  Dim sql As String

  sql = _
      "update my_table " & Chr$(10) & _
      "    set time = sysdate, " & Chr$(10) & _
      "    randfield1 = 'FAKE', " & Chr$(10) & _
      "    randfield5 = 'ME', " & Chr$(10) & _
      "    the_field8 = 'test' " & Chr$(10) & _
      "    where my_key = '84' " & Chr$(10) & _
      "    ;"

  Dim newSql
  newSql = sql
  newSql = Replace$(newSql, "where", ") and")
  newSql = Replace$(newSql, "update", "select count(*) from")
  newSql = Replace$(newSql, "set", "where (")
  newSql = Excel.Application.WorksheetFunction.Substitute(newSql, "=", Chr$(22), 5)
  newSql = Replace$(newSql, "=", "<>")
  newSql = Replace$(newSql, Chr$(22), "=")
  newSql = Replace$(newSql, ",", " or ")
  newSql = Replace$(newSql, "time <> sysdate or", vbNullString)

  MsgBox newSql

End Sub