Home » SQL & PL/SQL » SQL & PL/SQL » Carriage Return Finding (Oracle 11g,Windows)
Carriage Return Finding [message #675638] Thu, 11 April 2019 02:12 Go to next message
pstanand
Messages: 128
Registered: February 2005
Location: Chennai,India
Senior Member
Hi All,
I have the following table in which I have pfx_desc column is having carriage return value and so when the data is exported to excel it shows in two lines.
I used select pfx_type,bsbs_type,replace(pfx_desc,chr(13),' ')from carriage_return_test statement to remove the carriage return and make it a space and the data looks like below.
Quote:

S031 ANCO INN value appreciation OON value depreciation by 3%
S032 ACD INN value Assitive Communication OON assitive communication
But I need the data to be look like as below
Quote:

S031 ANCO INN value appreciation
OON value depreciation by 3%
S032 ACD INN value Assitive Communication
OON assitive communication
create table carriage_return_test(pfx_type varchar2(4), bsbs_type varchar2(4), pfx_desc varchar2(200));
insert into carriage_return_test values('S031','ANCO','INN value appreciation
OON value depreciation by 3%');
insert into carriage_return_test values('S032','ACD','INN value Assitive Communication
OON assitive communication');

Kindly let me know how can I achieve this format.

Thanks for your input and suggestion.

Anand
Re: Carriage Return Finding [message #675639 is a reply to message #675638] Thu, 11 April 2019 02:37 Go to previous messageGo to next message
John Watson
Messages: 7871
Registered: January 2010
Location: Global Village
Senior Member
I get this,
orclx>
orclx> set lin 300
orclx> drop table carriage_return_test;

Table dropped.

orclx> set lin 300
orclx> create table carriage_return_test(pfx_type varchar2(4), bsbs_type varchar2(4), pfx_desc varchar2(200));

Table created.

orclx> insert into carriage_return_test values('S031','ANCO','INN value appreciation
  2  OON value depreciation by 3%');

1 row created.

orclx> insert into carriage_return_test values('S032','ACD','INN value Assitive Communication
  2  OON assitive communication');

1 row created.

orclx> select * from carriage_return_test;

PFX_ BSBS PFX_DESC
---- ---- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
S031 ANCO INN value appreciation
          OON value depreciation by 3%

S032 ACD  INN value Assitive Communication
          OON assitive communication


orclx>
what is it that you are trying o achieve?
Re: Carriage Return Finding [message #675640 is a reply to message #675639] Thu, 11 April 2019 02:43 Go to previous messageGo to next message
pstanand
Messages: 128
Registered: February 2005
Location: Chennai,India
Senior Member
It is my bad. Initially the data is looks like below. So I used to replace function and got a space where the carriage return is there.
S031 ANCO INN value appreciationOON value depreciation by 3%

S032 ACD  INN value Assitive CommunicationOON assitive communication

After removing the carriage return I got like this.

S031 ANCO INN value appreciation OON value depreciation by 3%

S032 ACD  INN value Assitive Communication OON assitive communication

But I need to achieve as below.

S031 ANCO INN value appreciation
          OON value depreciation by 3%

S032 ACD  INN value Assitive Communication
          OON assitive communication


Re: Carriage Return Finding [message #675645 is a reply to message #675640] Thu, 11 April 2019 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 66325
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use, in the same way, REPLACE to put back the new line character.

Re: Carriage Return Finding [message #675646 is a reply to message #675645] Thu, 11 April 2019 03:39 Go to previous messageGo to next message
pstanand
Messages: 128
Registered: February 2005
Location: Chennai,India
Senior Member
Hi did you mean the following way?
select pfx_type,bsbs_type,replace(replace(pfx_desc,chr(13),' '),' ',chr(13))

This brings the older way.can you please provide the query
Re: Carriage Return Finding [message #675649 is a reply to message #675646] Thu, 11 April 2019 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 66325
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Put an actual test case of what you have and the actual result you want from it.
Give the rules to go from the test case to the result.

Re: Carriage Return Finding [message #675651 is a reply to message #675649] Thu, 11 April 2019 04:27 Go to previous messageGo to next message
pstanand
Messages: 128
Registered: February 2005
Location: Chennai,India
Senior Member
The original data looks likes this
S031 ANCO INN value appreciationOON value depreciation by 3%

S032 ACD  INN value Assitive CommunicationOON assitive communication

After removing the carriage return using 

select pfx_type,bsbs_type,replace(pfx_desc,chr(13),' ')from carriage_return_test I got like this.

S031 ANCO INN value appreciation OON value depreciation by 3%

S032 ACD  INN value Assitive Communication OON assitive communication

And I need to achieve as below.

S031 ANCO INN value appreciation
          OON value depreciation by 3%

S032 ACD  INN value Assitive Communication
          OON assitive communication
Re: Carriage Return Finding [message #675652 is a reply to message #675651] Thu, 11 April 2019 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 66325
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Isn't my sentence clear?
I don't care of what you originally had, this is irrelevant to the question.
I want you provide a test case what you have NOW.
And I want you provide the rules that is how do you want to split the data?
What you had before doesn't matter as Oracle does not know it and SQL can work on what it is and not on what it was and is no more.
Re: Carriage Return Finding [message #675653 is a reply to message #675651] Thu, 11 April 2019 04:32 Go to previous messageGo to next message
John Watson
Messages: 7871
Registered: January 2010
Location: Global Village
Senior Member
It looks as though your rule is that you want every OON to be preceded by a line break. Is that it?
Re: Carriage Return Finding [message #675658 is a reply to message #675653] Thu, 11 April 2019 04:56 Go to previous messageGo to next message
pstanand
Messages: 128
Registered: February 2005
Location: Chennai,India
Senior Member
Yes John. every OON is preceded with a line break.
Re: Carriage Return Finding [message #675661 is a reply to message #675652] Thu, 11 April 2019 05:05 Go to previous messageGo to next message
pstanand
Messages: 128
Registered: February 2005
Location: Chennai,India
Senior Member
Hi please find below my answers for your question

Available NOW
pfx_type    bsbs_type      pfx_desc

S031        ANCO           INN value appreciationOON value depreciation by 3%
S032        ACD            INN value Assitive CommunicationOON assitive communication

And the expected result would be

pfx_type    bsbs_type      pfx_desc

S031        ANCO           INN value appreciation 
                           OON value depreciation by 3%
S032        ACD            INN value Assitive Communication
                           OON assitive communication
Re: Carriage Return Finding [message #675662 is a reply to message #675661] Thu, 11 April 2019 05:08 Go to previous messageGo to next message
Michel Cadot
Messages: 66325
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is not the answer of my question which requires a test case.

Anyway, a simple REPLACE as I said can does the trick: you replace a space followed by OON by a carriage return followed by OON.

Re: Carriage Return Finding [message #675664 is a reply to message #675662] Thu, 11 April 2019 05:51 Go to previous messageGo to next message
pstanand
Messages: 128
Registered: February 2005
Location: Chennai,India
Senior Member
Hi thanks but I couldn't frame the query. can you help me please.
Re: Carriage Return Finding [message #675669 is a reply to message #675664] Thu, 11 April 2019 06:49 Go to previous messageGo to next message
flyboy
Messages: 1871
Registered: November 2006
Senior Member
Hi,

this seems to me like display issue. As John demonstrated, sqlplus displays correct result set.

Are you running that query in different tool, e.g. SQL Developer? (as asked e.g. here: https://stackoverflow.com/questions/41859187/can-we-display-chr10-newline-in-the-query-result-window-sql-developer)

In that case, you are probably out of luck. Anyway, I wonder, how you obtained results posted here, as copy/paste of the "Results" sheet preserves new lines like this:
S031	ANCO	"INN value appreciation
OON value depreciation by 3%"
S032	ACD	"INN value Assitive Communication
OON assitive communication"

[Edit: typos]

[Updated on: Thu, 11 April 2019 10:21]

Report message to a moderator

Re: Carriage Return Finding [message #675681 is a reply to message #675669] Thu, 11 April 2019 11:15 Go to previous messageGo to next message
Bill B
Messages: 1846
Registered: December 2004
Senior Member
Try changing the carriage return chr(13) to a line feed chr(10). That should work as a soft return within the cell

[Updated on: Thu, 11 April 2019 11:15]

Report message to a moderator

Re: Carriage Return Finding [message #675690 is a reply to message #675681] Fri, 12 April 2019 05:38 Go to previous message
pstanand
Messages: 128
Registered: February 2005
Location: Chennai,India
Senior Member
Hi I used listagg function to put the INN and OON in a single cell and then used instr to find the linebreak followed by OON.
After that I processed the records. Thanks for your support.
Previous Topic: Explain plan attached
Next Topic: Query running extremely slow
Goto Forum:
  


Current Time: Mon Apr 22 03:03:08 CDT 2019