Oracle SQL – Replace Newline Character

 

Requirement

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.

 

Solution

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
, Con.FAX_PH_NUM
, REPLACE(Con.FAX_PH_NUM, CHR(10), ‘ ‘) as “Updated_Fax”
, Con.COMMENTS
, REPLACE(Con.COMMENTS, CHR(13) || CHR(10), ‘ ‘) as “Updated_Comments”
from siebel.S_contact con;

 

Concept

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.

 

One response to this post.

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

    http://www.siebelnova.com/carriage-return

    Reply

Appreciate your feedback

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: