Back to cookbooks list Articles Cookbook

How to Replace a New Line in Oracle

Problem:

You’d like to remove a line break in a column.

Example:

Our database has a table named address_book with data in the columns city_name and company_address. You’d like to replace each of the tabs and the line breaks in the company_address column with ‘ ‘ (a space) to make it more visually appealing.

city_namecompany_address
Tokyo15th Oreo Street,

Tokyo

9870-11
Warsaw18 Marszalkowska Ave,

Warsaw

03-654
Accra123 Banana Junction,

Circle-Accra,

00244
Berlin25th Mango Avenue,

Asylum Down,

DE-1234

Solution:

Here’s the query:

SELECT
   city_name,
   REPLACE(company_address, CHR(10) || CHR(13) || CHR(9), ‘ ‘)
FROM address_book;

Here’s the result of the query:

city_namecompany_address
Tokyo15th Oreo Street, Tokyo 9870-11
Warsaw18 Marszalkowska Ave, Warsaw 03-654
Accra123 Banana Junction, Circle-Accra, 00244
Berlin25th Mango Avenue, Asylum Down, DE-1234

Discussion:

The REPLACE() function is generally used to replace all occurrences of a specified string in a string with another string. You may ask why we used CHR(10) and CHR(13) in the example above. The CHR() function is used to insert control characters into a string. CHR(10) is used to insert line breaks, CHR(9) is for tabs, and CHR(13) is for carriage returns.

In the example above, we wanted to remove all occurrences of the line break, of the tab, and of the carriage return, so we used CHR(10), CHR(9), and CHR(13). They can be combined using the concatenation sign in Oracle (||) for removing all of their occurrences in a column. An example syntax is REPLACE(string, CHR(10) || (CHR(13) || CHR(09), ‘ ‘ ).

Recommended courses:

Recommended articles:

See also: