CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloperline-breaksclob
icedmilocode
icedmilocode

Reputation: 95

Oracle select CLOB column still has line break even after using chr(10) and chr(13)

I have the following queries:

Query 1:

   select column1, column2 from table1 where column1 = 12345;

Results 1:

 column1|column2 
   12345|Topics briefed:
        |1) How to catch a fish
        |without using a fishing rod
        |2) How to cook a fish

Query 2:

select column1, REPLACE(REPLACE(column2, chr(10), ''), chr(13), '') as col2 from table1 where column1 = 12345;

Results 2:

 column1|col2 
   12345|Topics briefed: 1) How to catch a fish
        |without using a fishing rod 2) How to cook a fish

Results 2 is not what I wanted, I want everything to be in one row. How can I achieve the following results: 

 column1|col2 
   12345|Topics briefed: 1) How to catch a fish without using a fishing rod 2) How to cook a fish

Thanks in advance!

Upvotes: 0

Views: 1369

Answers (2)

eaolson
eaolson

Reputation: 15094

You don't say how you're displaying these results, but my guess is that you actually have stripped out the line breaks, but whatever you're using to display them is wrapping them. If you're in sqlplus, you can do set linesize 999 to make the line very wide and column column2 format a200 to make column 2 display 200 characters wide.

Upvotes: 1

iminiki
iminiki

Reputation: 2573

Maybe this would work:

select column1, REPLACE(REPLACE(REPLACE(column2, chr(10), ''), chr(13), ''), chr(09), '') as col2
from table1
where column1 = 12345;

Upvotes: 3

Related Questions