Home » SQL & PL/SQL » SQL & PL/SQL » How to replace the new line and line feed in a specific manner (oracle 11g)
How to replace the new line and line feed in a specific manner [message #670257] Wed, 20 June 2018 03:22 Go to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
Hi Team,


I have the below data in a column called as "DATA" in oracle.I was trying to remove the new empty line after each line .

Input :

This is a text from line 1.

This is text from line 2.

This is a text from line 3.The line 3 ends here .

This is a text from line 4.The line ends here .

output :=

This is a text from line 1.
This is text from line 2.
This is a text from line 3.The line 3 ends here .
This is a text from line 4.The line ends here .
I have tried using

Query :

Select regexp_replace(regexp_replace(data,chr(13),null),chr(10),null) from main_data;
When i execute the below query the output is like a paragraph .

Query Output:
This is a text from line 1.This is text from line 2.This is a text from line .The line 3 ends here .This is a text from line 4.The line ends here .

Can any one say how to achieve this ?

Thank you.
Re: How to replace the new line and line feed in a specific manner [message #670259 is a reply to message #670257] Wed, 20 June 2018 04:14 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
End Of Line markers are just that - every line has them, not just blank ones.
Your data is really
This is a text from line 1.chr(10)
chr(10)
This is text from line 2.chr(10)
chr(10)
This is a text from line 3.The line 3 ends here .chr(10)
chr(10)
This is a text from line 4.The line ends here .

If you replace every EOL with null then you get a single line.
To get rid of blank lines you need to replace two consecutive EOLs with a single EOL.
So either replace chr(10)||chr(10) with chr(10)
or replace chr(13)||chr(10)||chr(13)||chr(10) with chr(13)||chr(10)
depending on whether you have unix or dos line endings.
Re: How to replace the new line and line feed in a specific manner [message #670261 is a reply to message #670257] Wed, 20 June 2018 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (
  2  select 'This is a text from line 1.
  3
  4  This is text from line 2.
  5
  6  This is a text from line 3.The line 3 ends here .
  7
  8  This is a text from line 4.The line ends here .
  9  ' val from dual )
 10  select regexp_replace(val,chr(13)||'?'||chr(10)||'{2,}',chr(10)) from data;
REGEXP_REPLACE(VAL,CHR(13)||'?'||CHR(10)||'{2,}',CHR(10))
--------------------------------------------------------------------------------------------
This is a text from line 1.
This is text from line 2.
This is a text from line 3.The line 3 ends here .
This is a text from line 4.The line ends here .
Re: How to replace the new line and line feed in a specific manner [message #670263 is a reply to message #670261] Wed, 20 June 2018 06:30 Go to previous messageGo to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
Hi Michel Cadot,

Thank you for your response.This query works perfectly for me .


Thank you.
Re: How to replace the new line and line feed in a specific manner [message #670264 is a reply to message #670263] Wed, 20 June 2018 06:46 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
Just for fun without regular expressions and assuming CHR(0) can't appear in text:

with data as (
select 'This is a text from line 1.

This is text from line 2.

This is a text from line 3.The line 3 ends here .

This is a text from line 4.The line ends here .
' val from dual )
select  replace(replace(replace(val,chr(10),chr(10) || chr(0)),chr(0) || chr(10)),chr(0)) val
  from  data
/

VAL
--------------------------------------------------
This is a text from line 1.
This is text from line 2.
This is a text from line 3.The line 3 ends here .
This is a text from line 4.The line ends here .


SQL> 

SY.
Re: How to replace the new line and line feed in a specific manner [message #670328 is a reply to message #670259] Thu, 28 June 2018 01:43 Go to previous message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
Thanks for the response . This answer's works for me and i got to know how can i solve that question in multiple ways .

[Updated on: Thu, 28 June 2018 01:45]

Report message to a moderator

Previous Topic: Fetching Unix Folder Name and Timestamp in SQL/PLSQL
Next Topic: Alter Table <<table name>> add partition by range does not work
Goto Forum:
  


Current Time: Fri Sep 18 23:31:34 CDT 2020