How to remove or skip the multiple new line in string?

user_8153866 0
Viewed 56
DECLARE
   v_string VARCHAR2(4000) := 'A database is an organized collection of data, 
   generally stored and accessed electronically 
   from a computer system. Where databases are more
  
  
   
  complex they are often developed 
  using formal design and modeling techniques.';
   v_sql_code_new VARCHAR2(4000);
BEGIN
END;
 /  

  Desired output: 
     A database is an organized collection of data, 
     generally stored and accessed electronically 
     from a computer system. Where databases are more
     complex they are often developed 
     using formal design and modeling techniques.

I am trying to remove all newlines from a string. I tried using regexp_replace but I am unable to get the desired result. Thank you in advanced

oracle12c plsql oracle
1 Answers

Though you can also use '^\s*$' with modifier=>'mn', the easiest way is to replace multiple chr(10):

DECLARE
   v_string VARCHAR2(4000) := 'A database is an organized collection of data, 
   generally stored and accessed electronically 
   from a computer system. Where databases are more
  
  
   
  complex they are often developed 
  using formal design and modeling techniques.';
   v_sql_code_new VARCHAR2(4000);
BEGIN
   dbms_output.put_line(regexp_replace(v_string,chr(10)||'(\s*'||chr(10)||')+',chr(10)));
END;
/

\s* here is just to remove lines containing only space characters.

user_4291000
Related