Removing like breaks from Oracle column data Oracle Database Tips by Donald Burleson

 

 

Question: I want to remove the line feed characters from data inside my tables and chop-off everything after the end-of-line character.  I'd like to rtrim this selection for line breaks, but I don't know how to remove the line feed characters:

select
   navn
from
   arkivenhet
where
   sti like '%X-4631/Dca%';
 

 

Answer: Oracle has many ways to solve this, and you can change column data with an update statement to replace any ASCII character:

 

UPDATE
   mytab
SET
   col1 = REPLACE(col1, '[CR-LF]', chr(13) );

 

If your trailing character has some definite length, you can remove them by doing substring like this:

select
   substr('remove+last+four+$$$$',0,length('remove+last+four+$$$$')-4)
from dual;
 

In Linux/UNIX, you can find the last character with a line break:

- ASCII 10
- LF
- ^J or ^M
- \n

For example, I remove line breaks from flat files in vi with:

<esc>:.,/s/<cntl>v<cntl>M//g

For removing line feed data from Oracle data columns:

1 - First, get the ASCII character that you want to replace:

select dump(table_key) from table;

2 - Next, you can use the ASCII value as your delimiter to compute your desired last line character:

location of the line break = instr(ascii(10)

3 - Now, build an update chopping off the text with substr:

your desired line = substr(mycol,1,instr(ascii(10))

4 - Finally, plop that into an update, something like this:

update fred set mycol = substr(mycol,1,instr(ascii(10))

update mytable set mycol=replace(mycol,chr(ascii(10)),'')

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.