How to trim the new line character of column data in ctl file of SQL Loader

Question

My table data has contains new line character it is loading from sql loader ctl file, one column called 'IPADDRESS'is loading with new line character:

My ctl file :

 load data
 INFILE 'abc.txt'
 INTO TABLE TABLENAME
 APPEND
 FIELDS TERMINATED BY '\|'
 (MAKE,
 CUST_ID "UPPER(:CUST_ID)",
 IPADDRESS  "REGEXP_REPLACE(:IPADDRESS, '\\.\\D+', '', 1, 0)"
 )

Data in table storing is Ex:

Make CUST_ID        IPADDRESS 
------------------------------
C   MPG-VG-ALG01    "9.7.69.37
"
C   MPG-VG-ALG03    "9.7.69.39
"

Sample input file data :

C|mpg-vg-alg01.gdl.mex.ibm.com|9.7.69.37 
C|mpg-vg-alg03.gdl.mex.ibm.com|9.7.69.39 
C|mpg-vg-alg04.gdl.mex.ibm.com|9.7.69.23

You can replace new line, or carriage return by replacing `CHAR(13)` or `CHAR(10)` eg: REPLACE(REPLACE('column value', CHAR(13),''),CHAR(10),'')... or you could replace it in regex — Rohan Büchner, Apr 21 '16 at 09:23
C|mpg-vg-alg01.gdl.mex.ibm.com|9.7.69.37 C|mpg-vg-alg03.gdl.mex.ibm.com|9.7.69.39 C|mpg-vg-alg04.gdl.mex.ibm.com|9.7.69.23 — Chaya, Apr 21 '16 at 10:35
@Rohan Büchner, I have mentioned the sample file data in my question, edited..Please look into that — Chaya, Apr 21 '16 at 10:47

score 1 · Answer 1 · edited Apr 21 '16 at 17:48

1

Answer for my question is : column_name "REPLACE(:column_name,CHR(13),'')";

edited Apr 21 '16 at 17:48

Rohan Büchner

5,333
4
62
106

answered Apr 21 '16 at 11:14

Chaya

95
3
12

Barbaros Özhan · Answer 2 · 2021-12-15T06:29:32.863

Yes, one option would be using REPLACE() function but need to add more;

add CHAR(data_length) for string any data type even if it's of type VARCHAR2
add CHR(10)(line feed) also along with CHR(13)(carriage return)
don't forget to add TRIM() function nested within REPLACE() against extra issues too
using the third argument is redundant

such as

column_name CHAR(4000) "REPLACE(TRIM(:column_name),CHR(13)||CHR(10))"'

moreover

column_name CHAR(4000) "TRANSLATE(TRIM(:column_name),CHR(13)||CHR(10),' ')"'

might be used as an alternative.

How to trim the new line character of column data in ctl file of SQL Loader

2 Answers2