SQL / Regex挑战/难题：如何从SQL代码中删除注释（通过使用SQL查询）？ [英] SQL/Regex Challenge/Puzzle: How to remove comments from SQL code (by using SQL query)?

问题描述

• 单行注释（例如，我的注释）应删除。


• 多-行注释（例如/ *我的注释* /）应该删除。


• 字符串文字的内容（例如这是多行注释：/ *我的注释* /）


• 标识符的内容（例如-列1-）应该忽略。

• 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.

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

Literals and identifiers can span over multiple lines

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

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

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

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

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

以下代码无效，因为仅关闭了内部注释：

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

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

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

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 */.

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

但这是有效的代码：

/* comment /* still the same comment */

推荐答案

解决方案

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"*
                                /'

这篇关于SQL / Regex挑战/难题：如何从SQL代码中删除注释（通过使用SQL查询）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！