How to Replace a New Line in Oracle Database: Oracle Operators: REPLACE Table of Contents Problem: Example: Solution: Discussion: Problem: You’d like to remove a line break in a column in Oracle database. 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, Tokyo9870-11 Warsaw18 Marszalkowska Ave, Warsaw03-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 in Oracle is generally used to replace all occurrences of a specified string in a string with another string. It normally takes three arguments: the first is the string to replace a sequence of characters in, the second argument is the string that we want to replace, and the third argument is the replacement string. You may ask why we used CHR(10) and CHR(13) in the example above as the string we want to replace. 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. We want them to be replaced with a space, so the syntax used in the example is REPLACE(string, CHR(10) || (CHR(13) || CHR(09), ' '). Recommended courses: SQL Basics Standard SQL Functions SQL Practice Set Recommended articles: Standard SQL Functions Cheat Sheet 5 SQL Functions for Manipulating Strings See also: How to Extract a Substring From a String in Oracle or SQLite How to Split a String in Oracle Subscribe to our newsletter Join our monthly newsletter to be notified about the latest posts. Email address How Do You Write a SELECT Statement in SQL? What Is a Foreign Key in SQL? Enumerate and Explain All the Basic Elements of an SQL Query