12 Replies Latest reply: Feb 18, 2013 7:44 AM by ranit B RSS

    Reg : remove Newlines in CLOB -

    ranit B
      Hi Experts,

      I've a CLOB column where the data length is huge. So just posting below a small snapshot.
      --Load files               :
      --Create directory                    :
      --***************************************************
      
      
      
      --
      -- name  : raddo
      -- location    : cad
      -- id     : free
      --
      
      INSERT INTO fff.roller (roll_id,
                                           roll_name,
                                           roll_description,
      I need to remove multiple newlines and if possible want to bring all code ( which are not commented ) into a single row...

      Expected o/p :
      --Load files               :
      --Create directory                    :
      --***************************************************
      --
      -- name  : raddo
      -- location    : cad
      -- id     : free
      --
      INSERT INTO fff.roller (roll_id,roll_name,roll_description,
      Please let me know if this is feasible... and any suggestions is highly appreciated.

      Thanks
      Ranit B.
        • 1. Re: Reg : remove Newlines in CLOB -
          BluShadow
          ranit B wrote:
          Please let me know if this is feasible... and any suggestions is highly appreciated.
          Possible, yes... feasible... that's a different matter, and will depend on how much data you're processing and whether it's really worth it in the end, especially if you've got to take account of comments in the code.

          Why do you want to do this?
          • 2. Re: Reg : remove Newlines in CLOB -
            793996
            Also it doesnt seem to have any specific pattern for CHR(9) the new line character removal.

            Vivek
            • 3. Re: Reg : remove Newlines in CLOB -
              ranit B
              Why do you want to do this?
              Long story.. ;)

              My table creation scripts are stored in a CLOB col... I'm trying to retrieve them and using REGEX will check for some 'simple' coding standards like -
              INSERT INTO <schema_name>.<table_name>
              and not just <table_name>
              • 4. Re: Reg : remove Newlines in CLOB -
                BluShadow
                Vivek wrote:
                Also it doesnt seem to have any specific pattern for CHR(9) the new line character removal.

                Vivek
                chr(9) isn't a new line
                • 5. Re: Reg : remove Newlines in CLOB -
                  793996
                  My bad. Bit confused with CHR(9) and CHR(10) i.e. tab and new line characters respectively ;)

                  Vivek
                  • 6. Re: Reg : remove Newlines in CLOB -
                    ranit B
                    Is there any Regex to check -
                    Between 'INSERT INTO' and '(' if any dot is present. This can be the check for <schema_name>.<table_name>

                    But I'm not able to get the Regex properly.

                    Any help guys.
                    • 7. Re: Reg : remove Newlines in CLOB -
                      jeneesh
                      ranit B wrote:
                      Is there any Regex to check -
                      Between 'INSERT INTO' and '(' if any dot is present.
                      That will be possible. But what if the table name is in the next line?
                      insert into 
                      scott.test
                      ...
                      • 8. Re: Reg : remove Newlines in CLOB -
                        ranit B
                        For that, I'm doing
                        REPLACE(REPLACE(txt,CHR(10)), CHR(13))
                        This removes the newline and code comes in a single line... but then one more problem is multiple spaces between 'INTO' and 'SCOTT'

                        My try....so poor in Regexps :(
                        regexp_like(replace(replace(txt,CHR(10)), CHR(13)) ,'(INSERT INTO)(\s)+([[:alpha:]])+(.)([[:alpha:]])+');
                        Please help.
                        • 9. Re: Reg : remove Newlines in CLOB -
                          jeneesh
                          For this you don't need to remove new lines..But you you have to escape the dot (because it has a different meaning in the patterns..)
                          with t as 
                          (
                          select 'insert into test ' str from dual union all
                          select 'insert into 
                          scott.test ' str from dual union all
                          select 'insert into test values(''test.test'' ' str from dual
                          )
                          select *
                          from t
                          where regexp_like(str ,'(INSERT INTO)(\s)+([[:alpha:]])+(\.)([[:alpha:]])+','i');
                          
                          STR                                
                          ------------------------------------
                          insert into                          
                          scott.test  
                          but what about special characters (like underscore) in the table name or schema name..?
                          • 10. Re: Reg : remove Newlines in CLOB -
                            ranit B
                            This is kind-of my actual input :
                            with t as 
                            (
                            select /*'insert into a.test ' str from dual union all*/
                                'insert into 
                                scott.test 
                                asaf
                                asf
                                aghhh;
                            
                                insert into test' str from dual /*union all
                            select 'insert into test values(''test.test'' ' str from dual*/
                            )
                            I've inserted the complete set of Insert statements in a single CLOB column. How to get this correct?
                            Your query is not able to scan all patterns inside that column.
                            • 11. Re: Reg : remove Newlines in CLOB -
                              jeneesh
                              Like this?
                              with t as 
                              (
                              select 'insert into 
                                  scott.test 
                                  asaf
                                  asf
                                  aghhh;
                               
                                  insert into test' str from dual 
                              )
                              select regexp_replace(
                                          str ,
                                          '(INSERT INTO)(\s)+([[:alpha:]])+(\.)([[:alpha:]])+[^;]*;',
                                          '',1,1,'i') str
                              from t;
                              
                              STR                   
                              -----------------------
                              
                               
                                  insert into test 
                              • 12. Re: Reg : remove Newlines in CLOB -
                                ranit B
                                Thanks Jeneesh, but this query is removing only the first correct pattern. Why it is not checking for all the INSERTs there... there are around 10-12 INSERT statements stored in that col.

                                Also ,every INSERT statements are separated by few lines of comment '--'.

                                Might be coz of this the REGEXP is not working for all of the INSERTs.

                                Please help..