SQL / Regex挑战/难题：如何从SQL代码中删除注释（通过使用SQL查询）？

Requirements

• Single-lines comments (e.g. -- my comment) should be removed.

应删除单行评论(例如 - 我的评论)。

• Multi-line comments (e.g. /* my comment */) should be removed.

应删除多行评论(例如/ *我的评论* /)。

• The content of strings literals (e.g. 'this is a multi-line comment: /* my comment */') should be ignored.

字符串文字的内容(例如'这是一个多行评论:/ *我的评论* /')应该被忽略。

• The content of identifiers (e.g. "-- column 1 --") should be ignored.

应忽略标识符的内容(例如“ - 第1列 - ”)。

literals and identifiers

Literals and identifiers can span over multiple lines

文字和标识符可以跨越多行

Single-line comments

A single-line comment might be the last element of the code and might not end with a newline.

单行注释可能是代码的最后一个元素,可能不会以换行符结尾。

Nested multi-line comments

In databases such as SQL Server and PostgreSQL, multi-line comments can be nested, e.g -

在SQL Server和PostgreSQL等数据库中,可以嵌套多行注释,例如 -

/* outer comment /* inner comment */ */

The following code is invalid since only the inner comment is closed:

以下代码无效,因为只有内部注释被关闭:

/* opened outer comment /* closed inner comment */

In databases such as Teradata, Oracle, MySql and SQLite there is no concept of nested comments. The following code is invalid since the comment is already closed with the leftmost */.

在诸如Teradata,Oracle,MySql和SQLite等数据库中,没有嵌套注释的概念。以下代码无效,因为注释已使用最左侧的* /关闭。

/* comment /* is closed */ ERROR */

This however is a valid code:

但这是一个有效的代码:

/* comment /* still the same comment */

1 个解决方案

#1

Solutions

Teradata

with t (txt) as 
(
select     '
            select    /* comment /* yada yada yada /* / // bla bla bla  
                        1
                                    */ t1.i
                   ,''"SRC''''"''    as "This''is''the
                                ''source"

            from      t1 /* "Comment 2" - '' */ cross join t2 -- /* comment 3 */

            where     t2.v = ''/*DST"*
                                /'' -- comment 4'
)

select    regexp_replace (txt,'(''.*?''|".*?")|/\*.*?\*/|--.*?(?=[\r\n]|$)','\1',1,0,'n')     as clean_txt

from      t
;

Oracle

with t (txt) as 
(
select     '
            select    /* comment /* yada yada yada /* / // bla bla bla  
                        1
                                    */ t1.i
                   ,''"SRC''''"''    as "This''is''the
                                ''source"

            from      t1 /* "Comment 2" - '' */ cross join t2 -- /* comment 3 */

            where     t2.v = ''/*DST"*
                                /'' -- comment 4'

from        dual
)

select    regexp_replace (txt,'(''.*?''|".*?")|/\*.*?\*/|--.*?(?=$|\Z)','\1',1,0,'nm')

from      t
;

Result

            select     t1.i
                   ,'"SRC''"'    as "This'is'the
                                'source"

            from      t1  cross join t2 

            where     t2.v = '/*DST"*
                                /'