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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/334c3263054529b46354eb9aded2c0cc?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 #670264 is a reply to message #670263] |
Wed, 20 June 2018 06:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3280 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.
|
|
|
|