Search the Site

Sponsors

bottom corner

Remove unwanted line feeds in your data with Oracle SQL

At times you may find that line feeds in your data might negatively affect certain reports or output files that you may generate. This article shows how you can remove them.

This page is filed under keyword(s): oracle.

To remove these line feeds that you do not need, you will need to make use of the chr() function built-in to Oracle, which allows you to find special ASCII characters. Consider the case that a product's description may contain line feeds such as the following:

This is an amazing product.
It removes stains from your clothing, restoring crisp colors as if fresh from the store.
Buy it today!

select 
replace(
  replace(
    replace(
      product_description
    ,chr(10),' ') -- replace new lines with spaces
  ,chr(13),' ') -- replace carriage returns with spaces
,'  ',' ') as product_description -- cleans up double-spaces
from products
where product_id=1;

You should see that the line feeds are removed in the end result.

This is an amazing product. It removes stains from your clothing, restoring crisp colors as if fresh from the store. Buy it today!

Note that there is a replace() function that changes double-spaces to single-spaces. This may be needed because we may run into the case where there might be a trailing space at the end of a line, immediately before a line feed, which will become a double-space and may mess up your spacing slightly.

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 27 Aug 2008

bottom corner