Carriage Return Finding [message #675638] |
Thu, 11 April 2019 02:12 |
pstanand
Messages: 133 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 |
John Watson
Messages: 8951 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 |
pstanand
Messages: 133 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 #675646 is a reply to message #675645] |
Thu, 11 April 2019 03:39 |
pstanand
Messages: 133 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 #675651 is a reply to message #675649] |
Thu, 11 April 2019 04:27 |
pstanand
Messages: 133 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 #675661 is a reply to message #675652] |
Thu, 11 April 2019 05:05 |
pstanand
Messages: 133 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 #675690 is a reply to message #675681] |
Fri, 12 April 2019 05:38 |
pstanand
Messages: 133 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.
|
|
|