CODEWITHSUNDEEP
sqloracle-database
sofian
sofian

Reputation: 157

two lines are created in one cell

I have an sql code that is retreiving a row with more than one line, Is there a way to make it combine those two lines into one line.

select revertrsn  from LN08PENm where aano in(754,870,1136,1138,1139,1140)

the data coming in the row that I am retreiving is like this:

As per Mohammad Sulaiman's approval

by mail date 07/04/2009

And I want it to be in one line like this:

As per Mohammad Sulaiman's approval by mail date 07/04/2009

Upvotes: 0

Views: 114

Answers (2)

David Aldridge
David Aldridge

Reputation: 52336

A good approach to cleaning up this sort of mess is just to replace non-printing control codes in general.

update tab
set    col =  regexp_replace(col,'[:cntrl:]','')
where  col != regexp_replace(col,'[:cntrl:]','')

Upvotes: 1

Oscar Pérez
Oscar Pérez

Reputation: 4397

You could replace the newline character \n or char(10) with a whitespace character . For example:

select replace(revertrsn,chr(10),' ') as only_one_line
  from LN08PENm 
 where aano in(754,870,1136,1138,1139,1140)

Note that the throw-a-line instruction varies across operating systems. On Windows you would need to check for a carriage return followed by a newline: chr(13)||chr(10). Confusingly, the OS we need to woory about is the OS which loaded the data, not the OS we're using for output.

Upvotes: 2

Related Questions