sql - sed - replace multiline string between patterns in different lines

Question

I have few .sql files that contain comments marked as '/* */' Comment can start and end at the same line, but mostly there is a multiline comment.

[UPDATED] Example:

    /*=======WHATEVER=======*/
    SELECT * FROM TABLE 
    WHERE
        /* PERIOD_DTE IN
                (SELECT MAX(PERIOD_DTE) 
                 FROM TABLE WHERE PERIOD_DTE < '1900-01-01')
                 AND FIELD1 = 100
                 AND FIELD2 IS NULL
        */
    FIELD3 IS NULL

Is it possible to use sed function for replace that kind of comment as an empty string?

Answer 1

This will do it for your example (with GNU sed):

sed 's_*/_
*/_' your_file | sed '//*/,/*//d'

The ways it's working is as follows:

• In the first command I'm using _ as a pattern delimiter to avoid needing to escape the characters.
• The first command breaks any single line comment into a multiple line comment so that we can deal with one situation in the second command.
• The second command deletes everything including and between lines matching the patterns /* and */

So with the example input:

SELECT * FROM TABLE 
WHERE
    /* PERIOD_DTE IN
            (SELECT MAX(PERIOD_DTE) 
             FROM TABLE WHERE PERIOD_DTE < '1900-01-01')
             AND FIELD1 = 100
             AND FIELD2 IS NULL
    */
    /* SINGLE LINE COMMENT */
FIELD3 IS NULL

So the two steps are:

sed 's_*/_
*/_' your_file

outputs:

SELECT * FROM TABLE
WHERE
    /* PERIOD_DTE IN
            (SELECT MAX(PERIOD_DTE)
             FROM TABLE WHERE PERIOD_DTE < '1900-01-01')
             AND FIELD1 = 100
             AND FIELD2 IS NULL

*/
    /* SINGLE LINE COMMENT
*/
FIELD3 IS NULL

and

sed '//*/,/*//d'

outputs:

SELECT * FROM TABLE
WHERE
FIELD3 IS NULL