CODEWITHSUNDEEP
oracle-databaseoracle11g
Manikandan
Manikandan

Reputation: 11

Multiple lines in a column in oracle to a single row

My oracle table is as follows ( Address column having multiple lines): 

ID         Address
--------------------
1456897    No 61 
           11th Street
           Tatabad  Coimbatore - 641012

How to get the desired result as (with Address column as a single line) ?

ID         Address
-------------------------
1456897   No 61 , 11th Street, Tatabad  Coimbatore - 641012

Upvotes: 1

Views: 6536

Answers (2)

René Nyffenegger
René Nyffenegger

Reputation: 40613

I don't know if your database has its newlines as \x0a or \x0d or \x0d\x0a. I therefore propose a the following solution that handles all three kind of new lines. It will however replace mutliple newlines with one ,. This might be what you want, or it might not.

select
  id,
  regexp_replace(
     address, 
    '('||chr(10)||'|'||chr(13)||')+', 
    ', ') as address,
   .... 
 from
    ....

Upvotes: 2

i100
i100

Reputation: 4666

remove new line character in the column - something like

SELECT REPLACE(Address_column, '\n', ' ') -- \n might be also \r\n or even \r
  FROM table_name

Upvotes: 0

Related Questions