CODEWITHSUNDEEP
sql
user8659376
user8659376

Reputation: 399

How do I remove a line break (\n) in SQL?

I have a table of addresses that have certain addresses listed as 123 Clock St \nAPTB.

How do I remove the "\n" and have there be a space between the street address and apt number.

I tried using the Replace function, but it didn't seem to work. Am I formatting it correctly?

Here is what I was using: 

Select * from address_table;
UPDATE address_table SET address = replace (address, '\n', '');

Upvotes: 0

Views: 3865

Answers (2)

Carsten Massmann
Carsten Massmann

Reputation: 28196

The issue is probably the correct masking of you newline character. I have not yet found a solution for that but the following worked for me in Sql-server and MySql:

select replace(address,'
',' ');

In SQL it is permissible to have actual linefeeds in a string literal.

(I tried the otherwise suggested doubling of escape characters first, but for me it did not work it. Edit: The escape character solution does work too: http://rextester.com/IZCF14460 , but without doubling them.)

Upvotes: 0

p1erstef
p1erstef

Reputation: 394

As backslash ('\') is an escape character, you should double it :

Select * from address_table;
UPDATE address_table SET address = replace (address, '\\n', ' ');

Upvotes: 2

Related Questions