regex - Regular expression to remove comments from SQL statement

Question

I'm trying to come up with a regular expression to remove comments from an SQL statement.

This regex almost works:

(/*([^*]|[
]|(*+([^*/]|[
])))**+/)|'(?:[^']|'')*'|(--.*)

Excepth that last part doesn't handle "--" comments very well. The problem is handling SQL strings, delimited with ''.

For example, if i have

SELECT ' -- Hello -- ' FROM DUAL

It shouldn't match, but it's matching.

This is in ASP/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.

Answer 1

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 );