Java Regex find/replace pattern in SQL comments

Question

I want to find/replace a character/pattern ONLY inside SQL comments ( single line comments -- and block comments /* */). The source string is an SQL script.

At the moment I am searching for a semi-colon (;) within comments and want to replace it with blank space.

Source

CREATE OR REPLACE PROCEDURE TESTDBA.PROC_REUSING_BINDED_VAR_N_DSQL

 AS
        a NUMBER:=2;
        b NUMBER:=3; -- jladjfljaf; lakjflajf
-- alksdjflkjaf ladkjf 
        v_plsql_tx VARCHAR2(2000);
    begin
        v_plsql_tx := 'BEGIN ' || ' :1 := :1 + :2; ' || 'END;';
        execute immediate v_plsql_tx
        using in out a, b;
        insert into testdba.NEW_TESTING_TABLE(CHARACTER_VALUE) VALUES('a='||a);
   end PROC_REUSING_BINDED_VAR_N_DSQL;
-- lajdflajf

/*lakjdfljalfdk; alkdjf*/

/*asdf 
;
asdfa*/

/*
    adf
asd asdf


*/

Can you please suggest something.

Thanks

FailedDev · Accepted Answer · 2011-10-13 12:52:48Z

3

I would do this like this :

    try {
    Pattern regex = Pattern.compile("(?:/\\*[^;]*?\\*/)|(?:--[^;]*?$)", Pattern.DOTALL | Pattern.MULTILINE);
    Matcher regexMatcher = regex.matcher(subjectString);
    while (regexMatcher.find()) {
        // matched text: regexMatcher.group()
        // match start: regexMatcher.start()
        // match end: regexMatcher.end()
    } 
} catch (PatternSyntaxException ex) {
    // Syntax error in the regular expression
}

The above will give you all comments without ';'. Then I would iterate line by line through the sql file and when I encountered a line which had a comment I would check to see if that line is in my list of matches - if not then I would search replace ; with ' ' in the whole comment. Of course you will have to find where the comment ends but this is easy -- ends in the same line and /* and when the first */ is found. This way you can change any number of ; with the same code.

answered Oct 13, 2011 at 12:52

FailedDev

27k9 gold badges56 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ali Over a year ago

This explains in detail what I wanted and a solution is provided as well ostermiller.org/findcomment.html. But this solution has a big performance issues that is discussed here stackoverflow.com/questions/462843/…

Mike Henke Over a year ago

this seems to pickup items like </head> blah blah </body>

John B · Accepted Answer · 2011-10-13 12:33:02Z

1

Probably the best bet is to use two regexes and two Patterns (one single line and one multi-line).

Single Line: \\-\\-[^;]*(;) -- not sure the best way to find multiple ; within a line

Multi-line: /\\*[^;(\\*/)]*?(;)[^;]*?\\*/ -- something like this anyway

edited Oct 13, 2011 at 12:33

answered Oct 13, 2011 at 12:26

John B

33.1k8 gold badges80 silver badges100 bronze badges

Comments

0xCAFEBABE · Accepted Answer · 2011-10-13 12:49:05Z

What you need to find out first: There are two ways that multi-line comments can be handled.

A single "*/" closes all currently open "/*".
For every "/*" your need a corresponding "*/" (nested comments).

The first one is relatively easy to implement. The second can only be done by either deep magic regex (read: unmaintainable by future coders) or with a short program.

The first one is pretty easy: Using "/\*.*;.*\*/" will give you a match whenever there is an embedded semicolon.

The second one will need a bit of programming. If you encounter a ";", you will need to check if you're currently inside a comment. You can know by just sequentially reading the file (ignoring the carriage return/line feeds) and incrementing a number whenever you encounter a "/*" and decrement the number when encountering a "*/". If the number is at least 1, your semicolon is inside a comment.

Collectives™ on Stack Overflow

Java Regex find/replace pattern in SQL comments

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related