用正则表达式分析这个语句很简单自行设置正则表达式是否忽略大小写查询表达式 (select ([a-z]+,)*[a-z]+) (from [a-z]+) (where .+) (order by [a-z]+ (desc)?)替换表达式 $1 取得 “select id,title,author” $3 取得 “from eeggs” $4 取得 “where id=1 and author='HouRS'” $5 取得 “order by ID DESC”可以根据你的需要再改写,比这个再复杂的字符串也能分析出来
where和order by我希望是可选的!我试着在(order by [a-z]+ (desc)?)后面加上?后,$4就只能成为where id=1 and author='HouRS' order by ID DESC
To gaobud(真是搞不懂): 你自己测试过么?
根据gaobud的表达式我稍微改了一点! (select [\w,]+) (from [a-z]+) (where .+) (order by [a-z]+ (?:desc)?) 分析 “select id,title,author from eeggs where id=1 and author='HouRS' order by ID DESC”的结果为 $1 取得 “select id,title,author” $2 取得 “from eeggs” $3 取得 “where id=1 and author='HouRS'” $4 取得 “order by ID DESC” 但当我去掉order by ID DESC后就什么也找不到了! 在(order by [a-z]+ (?:desc)?)后加个?结果为 $1 取得 “select id,title,author” $2 取得 “from eeggs” $3 取得 “where id=1 and author='HouRS' order by ID DESC”
to: nonepassby(lcx) 我在http://www.dotnetcoders.com/web/Learning/Regex/RegexTester.aspx上测试了一下 结果为Match 1: select id,title from eeggs where id=1 and title="hours" order by id desc Group 1: select id,title from eeggs where id=1 and title="hours" order by id desc Group 2: Group 3: Group 4: select id,title Group 5: from eeggs where id=1 and title="hours" order by id desc Group 6: Group 7: 我该怎么改进一下!?
由于<where>和<order>组的可选(组后的?)会使用(.+)一直匹配下去,所以 Group 5 就成了 from eeggs where id=1 and title="hours" order by id desc
有什么办法使用(.+)在匹配到where、order时停止匹配吗?
这样写就可以(select [\w,]+) (from [a-z]+) (where (\w+[=<>]'?\w+'?)( ((and)|(or))( not)? (\w+[=<>]'?\w+'?))*)( order by [a-z]+ (?:desc)?)?$1取 “select id,title,author” $2取 “from eeggs” $3取 “where id=1 and author='HouRS'” $11取 “ order by ID DESC”不管后面有没有 order by ID DESC 都可以正确的取到 where id=1 and author='HouRS'你可以根据自已的需要继续修改
(select [\w,]+) select a,b,c, ??? 只匹配一个字符? select a,b 怎么办?where aa=11 and bb='11' 你的能匹配麽?order by 这一句更是一点约束都没有,语法错了都可。这样的话匹配出来有什么用?正则表达式最起码要保证statement符合正确的语法规则!其实这种东西用词法分析是最好,用正则表达式不是个好办法。你想一个表达式就把SQL语法给分析出来???规则太多,造成表达式太冗长,所以执行效率不会高。
我的怎么通不过了,试试看! Sub MatchRegex() ' Regex match Dim options As RegexOptions = RegexOptions.None Dim regex As Regex = New Regex("\s*(?<GP1>select\s*([a-z]+,)*[a-z]+)\s*(?<GP2>from\s*[a-z]+)\s*(?<GP3>\s*where\s*\w*\s*=\s*(('\w+')|([0-9]+))(\s*and\s*\w*\s*=\s*(('\w+')|([0-9]+)))*)\s*(?<GP4>ORDER\s*BY\s*\w*\s*(ASC|DESC)?(\s*,\s*\w*\s*(ASC|DESC)?)*)", options) Dim input As String = "select id,title,author from eeggs where id=1 and author='HouRS' ORDER BY ID DESC" ' Get match Dim match As Match = regex.Match(input) ' Named groups Dim groupA As String = match.Groups("GP1").Value Dim groupB As String = match.Groups("GP2").Value Dim groupC As String = match.Groups("GP3").Value Dim groupD As String = match.Groups("GP4").Value ' TODO: Do something with result System.Windows.Forms.MessageBox.Show(groupA, "Group: groupA") System.Windows.Forms.MessageBox.Show(groupB, "Group: groupB") End Sub
我是希望都有个通用的正则表达式来将字符串进行分组!!!
然后在依照分号读取就行了
SqlServer这样的服务器都是使用一个编译引擎做到的
(select ([a-z]+,)*[a-z]+) (from [a-z]+) (where .+) (order by [a-z]+ (desc)?)替换表达式
$1 取得 “select id,title,author”
$3 取得 “from eeggs”
$4 取得 “where id=1 and author='HouRS'”
$5 取得 “order by ID DESC”可以根据你的需要再改写,比这个再复杂的字符串也能分析出来
你自己测试过么?
(select [\w,]+) (from [a-z]+) (where .+) (order by [a-z]+ (?:desc)?)
分析
“select id,title,author from eeggs where id=1 and author='HouRS' order by ID DESC”的结果为
$1 取得 “select id,title,author”
$2 取得 “from eeggs”
$3 取得 “where id=1 and author='HouRS'”
$4 取得 “order by ID DESC”
但当我去掉order by ID DESC后就什么也找不到了!
在(order by [a-z]+ (?:desc)?)后加个?结果为
$1 取得 “select id,title,author”
$2 取得 “from eeggs”
$3 取得 “where id=1 and author='HouRS' order by ID DESC”
提取以下四个组:
select,from,where,order 取可
我在http://www.dotnetcoders.com/web/Learning/Regex/RegexTester.aspx上测试了一下
结果为Match 1: select id,title from eeggs where id=1 and title="hours" order by id desc
Group 1: select id,title from eeggs where id=1 and title="hours" order by id desc
Group 2:
Group 3:
Group 4: select id,title
Group 5: from eeggs where id=1 and title="hours" order by id desc
Group 6:
Group 7: 我该怎么改进一下!?
Group 5 就成了 from eeggs where id=1 and title="hours" order by id desc
$2取 “from eeggs”
$3取 “where id=1 and author='HouRS'”
$11取 “ order by ID DESC”不管后面有没有 order by ID DESC 都可以正确的取到 where id=1 and author='HouRS'你可以根据自已的需要继续修改
你的条件都满足咯,给分吧!
虽然后两位的表达式在我这没有通过,但给我了很多启示!!
经过改进目前我决定使用的正则表达式为(select [\w,]+) (from [a-z]+) ?(where [\w='"]+(?: (?:and|or|not) [\w'"=]+)*)? ?(order by [\w ]+)?where部分已经考虑到多个“and/or/not”的匹配!!
回去再好好研究一下,希望能把再多的SQL语法考虑进去!!分数我马上就给,因为是第一次提问,所以怎么给分也要研究一下!!!:)
再次感谢各位!
只匹配一个字符?
select a,b 怎么办?where aa=11 and bb='11' 你的能匹配麽?order by 这一句更是一点约束都没有,语法错了都可。这样的话匹配出来有什么用?正则表达式最起码要保证statement符合正确的语法规则!其实这种东西用词法分析是最好,用正则表达式不是个好办法。你想一个表达式就把SQL语法给分析出来???规则太多,造成表达式太冗长,所以执行效率不会高。
Sub MatchRegex()
' Regex match
Dim options As RegexOptions = RegexOptions.None
Dim regex As Regex = New Regex("\s*(?<GP1>select\s*([a-z]+,)*[a-z]+)\s*(?<GP2>from\s*[a-z]+)\s*(?<GP3>\s*where\s*\w*\s*=\s*(('\w+')|([0-9]+))(\s*and\s*\w*\s*=\s*(('\w+')|([0-9]+)))*)\s*(?<GP4>ORDER\s*BY\s*\w*\s*(ASC|DESC)?(\s*,\s*\w*\s*(ASC|DESC)?)*)", options)
Dim input As String = "select id,title,author from eeggs where id=1 and author='HouRS' ORDER BY ID DESC" ' Get match
Dim match As Match = regex.Match(input) ' Named groups
Dim groupA As String = match.Groups("GP1").Value
Dim groupB As String = match.Groups("GP2").Value
Dim groupC As String = match.Groups("GP3").Value
Dim groupD As String = match.Groups("GP4").Value ' TODO: Do something with result
System.Windows.Forms.MessageBox.Show(groupA, "Group: groupA")
System.Windows.Forms.MessageBox.Show(groupB, "Group: groupB")
End Sub