从 SQL 语句中删除注释的正则表达式 [英] Regular expression to remove comments from SQL statement
问题描述
我正在尝试提出一个正则表达式来从 SQL 语句中删除注释.
I'm trying to come up with a regular expression to remove comments from an SQL statement.
这个正则表达式几乎有效:
This regex almost works:
(/\*([^*]|[\r\n]|(\*+([^*/]|[\r\n])))*\*+/)|'(?:[^']|'')*'|(--.*)
除了最后一部分不能很好地处理--"注释.问题是处理用 '' 分隔的 SQL 字符串.
Excepth that last part doesn't handle "--" comments very well. The problem is handling SQL strings, delimited with ''.
例如,如果我有
SELECT ' -- Hello -- ' FROM DUAL
它不应该匹配,但它匹配.
It shouldn't match, but it's matching.
这是在 ASP/VBscript 中.
This is in ASP/VBscript.
我考虑过从右到左匹配,但我认为 VBScript 的正则表达式引擎不支持它.还尝试摆弄负向后视,但结果并不好.
I've thought about matching right-to-left but i don't think the VBScript's regex engine supports it. Also tried fiddling with negative lookbehind but the results weren't good.
推荐答案
在 PHP 中,我使用此代码取消 SQL 注释:
In PHP, i'm using this code to uncomment SQL:
$sqlComments = '@(([\'"]).*?[^\\\]\2)|((?:\#|--).*?$|/\*(?:[^/*]|/(?!\*)|\*(?!/)|(?R))*\*\/)\s*|(?<=;)\s+@ms';
/* Commented version
$sqlComments = '@
(([\'"]).*?[^\\\]\2) # $1 : Skip single & double quoted expressions
|( # $3 : Match comments
(?:\#|--).*?$ # - Single line comments
| # - Multi line (nested) comments
/\* # . comment open marker
(?: [^/*] # . non comment-marker characters
|/(?!\*) # . ! not a comment open
|\*(?!/) # . ! not a comment close
|(?R) # . recursive case
)* # . repeat eventually
\*\/ # . comment close marker
)\s* # Trim after comments
|(?<=;)\s+ # Trim after semi-colon
@msx';
*/
$uncommentedSQL = trim( preg_replace( $sqlComments, '$1', $sql ) );
preg_match_all( $sqlComments, $sql, $comments );
$extractedComments = array_filter( $comments[ 3 ] );
var_dump( $uncommentedSQL, $extractedComments );
这篇关于从 SQL 语句中删除注释的正则表达式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!