Remove newlines in oracle sql

Question

Currently in address column in test table,i have data in following format,

12th street

Test avenue

Test_City

but in the output,i would require it in following format,

12th street Test avenue Test_City.

Could any one please tell me the query to use to display it in the required manner.

Answer 1

You can try this:

select regexp_replace(your_address,'[[:space:]]',' ') from your_tab; 

Answer 2

Just strip chr(13) and chr(10) from the string:

declare
   teststring   varchar2 (32767) := ' This is the value

that I chose';
begin
   dbms_output.put_line (teststring);
   dbms_output.put_line (replace (replace (teststring, chr (13), ''), chr (10), ' '));
end;

The result:

 This is the value

that I chose
 This is the value  that I chose

Two spaces since I put in two returns in the text.

Answer 3

Try this:

translate(' example ', chr(10) || chr(13) || chr(09), ' ') 

The above will replace all line breaks (chr(10)), all tabs (chr(09)) and all carriage returns (chr(13)) with a space (' ').

Just replace ' example ' with your field name on wish you want to have the characters removed.