如何从一段sql语句中 取得所有的表名?
好像很简单. 但我做了一上午也没做出来考虑到通用性. 一条语句可能有 join, where, group by, order by.
但一定有 from 可不是简单的把 from 后面的 string 拿出来就可以的.比如下面的string 注意: 表名中有from单词SELECT [PEOPLE FROM USA].[ID], [PEOPLE FROM USA].[PEOPLE NAME]
FROM [PEOPLE FROM USA]
left join table1 on ...
right join table2 on ...
where ...
group by ...
order by ...如何 从里面提取出表名呢?不知道能不能用正则表达式, 但如果可以用的话 该如何写呢?谢谢!
好像很简单. 但我做了一上午也没做出来考虑到通用性. 一条语句可能有 join, where, group by, order by.
但一定有 from 可不是简单的把 from 后面的 string 拿出来就可以的.比如下面的string 注意: 表名中有from单词SELECT [PEOPLE FROM USA].[ID], [PEOPLE FROM USA].[PEOPLE NAME]
FROM [PEOPLE FROM USA]
left join table1 on ...
right join table2 on ...
where ...
group by ...
order by ...如何 从里面提取出表名呢?不知道能不能用正则表达式, 但如果可以用的话 该如何写呢?谢谢!
明摆着from是关键字,用个F代替难道你不认识吗?
自己给自己制造麻烦
MatchCollection mc = reg.Matches(yourStr);
foreach (Match m in mc)
{
richTextBox2.Text += m.Groups[1].Value + "\n";
}有不满足的给出测试用例这里没有考虑表名前带数据库名的,如需考虑,说明一下
谢谢 lxcnn我想 join 后面的表名 应该可以类似的拿出来 测试中!
可否给一个正则, 能找到所有表名的, 包括 left join, right join , Join, 后面的表名呢?
谢谢
现在工作中几乎不接触代码,对SQL更是忘得不知道是啥了
所以还要楼主提供测试用例,覆盖所有可能情况,我根据测试用例来写正则吧
一个专门用来解析SQL语句的DLL,网上有源码下载,使用方法很简单,例如我下面的例子取出查询的字段和参数
lblResult.Text = ""; TGSqlParser parser = new TGSqlParser(TDbVendor.DbVMssql);
parser.SqlText.Text = txtSQL.Text;
parser.Parse(); if (parser.ErrorCount > 0)
{
lblResult.Text += "发现了" + parser.ErrorCount.ToString() + "个错误。<br/>";
lblResult.Text += "错误信息:<br/>";
lblResult.Text += parser.ErrorMessages.Replace("\r\n", "<br/>");
lblResult.Text += "<br/><br/>";
} TLzStatementList stmts = parser.SqlStatements;
for (int i = 0; i < stmts.Count(); i++)
{
TCustomSqlStatement stmt = stmts[i]; lblResult.Text += "第" + (i + 1).ToString() + "个语句:" + stmt.SqlStatementType.ToString() + "<br/>"; if (stmt.SqlStatementType == TSqlStatementType.sstSelect)
{
lblResult.Text += "查询结果字段:"; for (int j = 0; j < stmt.Fields.Count(); j++)
{
TLzField field = stmt.Fields[j];
if (field.IsHasAs && field.FieldAlias != "")
{
lblResult.Text += field.FieldAlias + ",";
}
else
{
lblResult.Text += field.FieldName + ",";
}
} lblResult.Text += "<br/>";
} lblResult.Text += "参数:"; for (int k = 0; k < stmt.SqlVars.Count(); k++)
{
TLzValue value = stmt.SqlVars[k];
lblResult.Text += value.ValueName + ",";
}
lblResult.Text += "<br/>"; lblResult.Text += "<br/>";
}
如果是针对特定T-SQL -->正则很容易搞定.
假设以下语句可以执行SELECT [Van001 from US].Loan, [Van001 from US].FileDate, [Van001 from US].Val
FROM [Van001 from US]
INNER JOIN Table1 ON [Van001 from US].PropState = Table1.STATE
left join [table2 by US] on [Van001 from US].month = [table2 by US].month
right join [table3 join US] on [Van001 from US].year = [table3 join US].year
where [Van001 from US].year > 2000
group by [Van001 from US].loan
order by [Van001 from US].val谢谢!
不一定的access 我也不很懂. 只觉得它的sql 太"高调"
我这怎么看不到添加代码 改文字颜色的工具条了呢?
要提取出来这些表名
FROM [Van001 from US] 中的 [Van001 from US]
INNER JOIN Table1 ON 中的 Table1
left join [table2 by US] 中的 [table2 by US]
right join [table3 join US] 中的 [table3 join US]
SELECT [Van001 from US].Loan, [Van001 from US].FileDate, [Van001 from US].Val
FROM [Van001 from US]
INNER JOIN Table1 ON [Van001 from US].PropState = Table1.STATE
left join [table2 by US] on [Van001 from US].month = [table2 by US].month
right join [table3 join US] on [Van001 from US].year = [table3 join US].year
where [Van001 from US].year > 2000
group by [Van001 from US].loan
order by [Van001 from US].val
用chrome 习惯了.
MatchCollection mc = reg.Matches(yourStr);
foreach (Match m in mc)
{
richTextBox2.Text += m.Groups["table"].Value + "\n";
}
结果
[Van001 from US]
Table1
[table2 by US]
[table3 join US]啥也不说了 眼泪哇哇的!
谢谢 lxcnn! 欢迎大家继续讨论 稍后结贴!