Regex to remove single-line SQL comments (--)

.net, c#, regex, sql, vb.net

Solution

I will disappoint all of you. This can't be done with regular expressions. Sure, it's easy to find comments not in a string (that even the OP could do), the real deal is comments in a string. There is a little hope of the look arounds, but that's still not enough. By telling that you have a preceding quote in a line won't guarantee anything. The only thing what guarantees you something is the oddity of quotes. Something you can't find with regular expression. So just simply go with non-regular-expression approach.

EDIT: Here's the c# code:

        String sql = "--this is a test\r\nselect stuff where substaff like '--this comment should stay' --this should be removed\r\n";
        char[] quotes = { '\'', '"'};
        int newCommentLiteral, lastCommentLiteral = 0;
        while ((newCommentLiteral = sql.IndexOf("--", lastCommentLiteral)) != -1)
        {
            int countQuotes = sql.Substring(lastCommentLiteral, newCommentLiteral - lastCommentLiteral).Split(quotes).Length - 1;
            if (countQuotes % 2 == 0) //this is a comment, since there's an even number of quotes preceding
            {
                int eol = sql.IndexOf("\r\n") + 2;
                if (eol == -1)
                    eol = sql.Length; //no more newline, meaning end of the string
                sql = sql.Remove(newCommentLiteral, eol - newCommentLiteral);
                lastCommentLiteral = newCommentLiteral;
            }
            else //this is within a string, find string ending and moving to it
            {
                int singleQuote = sql.IndexOf("'", newCommentLiteral);
                if (singleQuote == -1)
                    singleQuote = sql.Length;
                int doubleQuote = sql.IndexOf('"', newCommentLiteral);
                if (doubleQuote == -1)
                    doubleQuote = sql.Length;

                lastCommentLiteral = Math.Min(singleQuote, doubleQuote) + 1;

                //instead of finding the end of the string you could simply do += 2 but the program will become slightly slower
            }
        }

        Console.WriteLine(sql);

What this does: find every comment literal. For each, check if it's within a comment or not, by counting the number of quotes between the current match and the last one. If this number is even, then it's a comment, thus remove it (find first end of line and remove whats between). If it's odd, this is within a string, find the end of the string and move to it. Rgis snippet is based on a wierd SQL trick: 'this" is a valid string. Even tho the 2 quotes differ. If it's not true for your SQL language, you should try a completely different approach. I'll write a program to that too if that's the case, but this one's faster and more straightforward.

Problem

Question: Can anybody give me a working regex expression (C#/VB.NET) that can remove single line comments from a SQL statement ? I mean these comments: ``` -- This is a comment ``` not those ``` /* this is a comment */ ``` because I already can handle the star comments. I have a made a little parser that removes those comments when they are at the start of the line, but they can also be somewhere after code or worse, in a SQL-string `'hello --Test -- World'` Those comments should also be removed (except those in a SQL string of course - if possible). Surprisingly I didn't got the regex working. I would have assumed the star comments to be more difficult, but actually, they aren't. As per request, here my code to remove /**/-style comments (In order to have it ignore SQL-Style strings, you have to subsitute strings with a uniqueidentifier (i used 4 concated), then apply the comment-removal, then apply string-backsubstitution. ``` static string RemoveCstyleComments(string strInput) { string strPattern = @"/[*][\w\d\s]+[*]/"; //strPattern = @"/\*.*?\*/"; // Doesn't work //strPattern = "/\\*.*?\\*/"; // Doesn't work //strPattern = @"/\*([^*]|[\r\n]|(\*+([^*/]|[\r\n])))*\*+/ "; // Doesn't work //strPattern = @"/\*([^*]|[\r\n]|(\*+([^*/]|[\r\n])))*\*+/ "; // Doesn't work // http://stackoverflow.com/questions/462843/improving-fixing-a-regex-for-c-style-block-comments strPattern = @"/\*(?>(?:(?>[^*]+)|\*(?!/))*)\*/"; // Works ! string strOutput = System.Text.RegularExpressions.Regex.Replace(strInput, strPattern, string.Empty, System.Text.RegularExpressions.RegexOptions.Multiline); Console.WriteLine(strOutput); return strOutput; } // End Function RemoveCstyleComments ```

Original source

Related problems