convert multiline column one line column in oracle

November 29, 2021

I have a table that contains three columns but one of them contains multiline text.I want to create another table from that row but adress column should be one line.

My table is Student

Table: Student

id  |  name  | address    
 1  |  John  | London Street`               
               Capital square Number:3`

What I want to do is to create another table like that(Address column should be one line)

id | name | address 
1  | John | London Street Capital square Number:3

My query is like that:

create table StudentTest as (select * from Student where id = 1)

Do you have any idea for PL/SQL?

>Solution :

A simple replace should do:

Source:

SQL> select * From test;

        ID NAME ADDRESS
---------- ---- -------------------------------------
         1 John London Street
                Capital square Number:3

Query:

SQL> select id, name, replace(address, chr(10), ' ') address
  2  from test;

        ID NAME ADDRESS
---------- ---- -------------------------------------
         1 John London Street Capital square Number:3

SQL>