Oracle SQL – Replace Newline Character



I had a requirement to take extract of all Contacts from Siebel Database (Oracle Database in this case). I designed the SQL including all necessary columns and took the extract in CSV format.

There were two challenges the I faced:

  • Linebreaks in the phone fields like Work Phone#, Fax Phone#, Home Phone#.
  • Linebreaks in the comments fields.

Due to this reasons, a single contact record was getting scattered to more than two lines in text file. This led to inconsistency.



Replace Function was used to resolve it. The syntax is:

replace( string1, string_to_replace, [ replacement_string ] )

where, string1 is the string to replace a sequence of characters with another set of characters.
string_to_replace is the string that will be searched for in string1.
replacement_string is optional. All occurrences of string_to_replace will be replaced by it in string1.

Following SQL was used:

select con.row_id
, REPLACE(Con.FAX_PH_NUM, CHR(10), ‘ ‘) as “Updated_Fax”
, REPLACE(Con.COMMENTS, CHR(13) || CHR(10), ‘ ‘) as “Updated_Comments”
from siebel.S_contact con;



Line Feed (LF) character (\n) has 10 as ASCII code, whereas Carriage Return (CR) character (\r) has 13 as ASCII code.

In Unix system, newline character is LF. But in Microsoft Windows system, CR+LF represents the newline character.


4 responses to this post.

  1. Nice article, you can also find some interesting info on implementing carriage return in workflows here :


  2. Hi admin, i must say you have very interesting articles here.
    Your blog should go viral. You need initial traffic boost only.
    How to get it? Search for; Mertiso’s tips go viral


  3. Posted by Vitali Yelkin on December 18, 2018 at 1:30 PM

    you are the KING!!!!
    it was very helpful!!!!


  4. Posted by Praveenkr on January 5, 2019 at 5:07 AM

    good explanation


Appreciate your feedback

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: