Sql/regex Challenge/puzzle: How To Remove Comments From Sql Code (by Using Sql Query)?

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. 'th

Solution 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  crossjoin t2 

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