我将这段代码用作Excel宏来解析选择和提取表名。
我的解析假设不使用语法select from a, b, c
。
只需对您的SQL
查询运行它,如果您对结果不满意,那么您离期望的结果只有几行代码。只需调试并相应地修改代码即可。
Sub get_tables()
sql_query = Cells(5, 1).Value
tables = ""
'get all tables after from
sql_from = sql_query
While InStr(1, UCase(sql_from), UCase("from")) > 0
i = InStr(1, UCase(sql_from), UCase("from"))
sql_from = Mid(sql_from, i + 5, Len(sql_from) - i - 5)
i = InStr(1, UCase(sql_from), UCase(" "))
While i = 1
sql_from = Mid(sql_from, 2, Len(sql_from) - 1)
i = InStr(1, UCase(sql_from), UCase(" "))
end
i = InStr(1, sql_join, Chr(9))
While i = 1
sql_join = Mid(sql_join, 2, Len(sql_join) - 1)
i = InStr(1, sql_join, Chr(9))
end
a = InStr(1, UCase(sql_from), UCase(" "))
b = InStr(1, sql_from, Chr(10))
c = InStr(1, sql_from, Chr(13))
d = InStr(1, sql_from, Chr(9))
MinC = a
If MinC > b And b > 0 Then MinC = b
If MinC > c And c > 0 Then MinC = c
If MinC > d And d > 0 Then MinC = d
tables = tables + "[" + Mid(sql_from, 1, MinC - 1) + "]"
end
'get all tables after join
sql_join = sql_query
While InStr(1, UCase(sql_join), UCase("join")) > 0
i = InStr(1, UCase(sql_join), UCase("join"))
sql_join = Mid(sql_join, i + 5, Len(sql_join) - i - 5)
i = InStr(1, UCase(sql_join), UCase(" "))
While i = 1
sql_join = Mid(sql_join, 2, Len(sql_join) - 1)
i = InStr(1, UCase(sql_join), UCase(" "))
end
i = InStr(1, sql_join, Chr(9))
While i = 1
sql_join = Mid(sql_join, 2, Len(sql_join) - 1)
i = InStr(1, sql_join, Chr(9))
end
a = InStr(1, UCase(sql_join), UCase(" "))
b = InStr(1, sql_join, Chr(10))
c = InStr(1, sql_join, Chr(13))
d = InStr(1, sql_join, Chr(9))
MinC = a
If MinC > b And b > 0 Then MinC = b
If MinC > c And c > 0 Then MinC = c
If MinC > d And d > 0 Then MinC = d
tables = tables + "[" + Mid(sql_join, 1, MinC - 1) + "]"
end
tables = Replace(tables, ")", "")
tables = Replace(tables, "(", "")
tables = Replace(tables, " ", "")
tables = Replace(tables, Chr(10), "")
tables = Replace(tables, Chr(13), "")
tables = Replace(tables, Chr(9), "")
tables = Replace(tables, "[]", "")
End Sub