Posts Tagged ‘newline’

Oracle SQL – Remove Trailing Zeroes From Phone Columns

 

Requirement

I designed the sql to extract Contacts from Siebel Database (Oracle Database in this case) including phone columns. The challenge that I faced with phone columns:

  • Trailing zeroes (Used by Siebel to capture phone number format)

My extract was not supposed to contain them.

 

Concept

Siebel puts a Newline character between actual phone data and its format.

Also, in my case, Siebel database was installed on a Unix server. In Unix, Newline character is represented by LineFeed (LF) character (\n), Its ASCII code is 10

 

Solution

I put up three in-built SQL library functions to use as follow:

Select
  PC.WORK_PH_NUM “Full WorkPhone#”,
  Case When INSTR(PC.WORK_PH_NUM,CHR(10)) > 0
     Then SUBSTR(PC.WORK_PH_NUM,0,INSTR(PC.WORK_PH_NUM,CHR(10))-1)
     Else PC.WORK_PH_NUM
  End “Updated WorkPhone#”
From Siebel.S_Contact PC;

 

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.