Regular expression to remove comments from SQL sta

Question

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])))*\*+/)|'(?:[^']|'')*'|(--.*)

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

Originally, I used @Adrien Gibrat's solution. However, I came across a situation where it wasn't parsing quoted strings, properly, if I had anything with a preceding '--' inside of them. I ended up writing this, instead:

'[^']*(?!\\)'(*SKIP)(*F)       # Make sure we're not matching inside of quotes
|(?m-s:\s*(?:\-{2}|\#)[^\n]*$) # Single line comment
|(?:
  \/\*.*?\*\/                  # Multi-line comment
  (?(?=(?m-s:\h+$))         # Get trailing whitespace if any exists and only if it's the rest of the line
    \h+
  )
)

# Modifiers used: 'xs' ('g' can be used as well, but is enabled by default in PHP)

Please note that this should be used when PCRE is available. So, in my case, I'm using a variation of this in my PHP library.

Example

Answer 2

remove /**/ and -- comments

function unComment($sql){

        $re = '/(--[^\n]*)/i';
        $sql = preg_replace( $re, '', $sql );

        $sqlComments = '@(([\'"]).*?[^\\\]\2)|((?:\#|--).*?$|/\*(?:[^/*]|/(?!\*)|\*(?!/)|(?R))*\*\/)\s*|(?<=;)\s+@ms';
        $uncommentedSQL = trim( preg_replace( $sqlComments, '$1', $sql ) );
        preg_match_all( $sqlComments, $sql, $comments );
        $sql = preg_replace('/[\x00-\x1F\x80-\xFF]/', '', trim($uncommentedSQL));


        return $sql;
    }