从 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屋！