Adding a line break in MySQL INSERT INTO text

Question

Could someone tell me how to add a new line in a text that I enter in a MySql table?

I tried using the '\n' in the line I entered with INSERT INTO statement but '\n' is shown as it is.

Actually I have created a table in MS Access with some data. MS Access adds new line with '\n'. I am converting MS Access table data into MySql . But when I convert, the '\n' is ignored and all the text is shown in one single line when I display it from MySql table on a PHP form.

Can anyone tell me how MySQL can add a new line in a text? Awaiting response, thanks!!

Find answer here http://stackoverflow.com/a/41448185/5466401 — Sibin John Mattappallil, Jan 03 '17 at 16:41

score 89 · Answer 1 · edited May 23 '17 at 12:10

89

If you're OK with a SQL command that spreads across multiple lines, then oedo's suggestion is the easiest:

INSERT INTO mytable (myfield) VALUES ('hi this is some text
and this is a linefeed.
and another');

I just had a situation where it was preferable to have the SQL statement all on one line, so I found that a combination of CONCAT_WS() and CHAR() worked for me.

INSERT INTO mytable (myfield) VALUES (CONCAT_WS(CHAR(10 using utf8), 'hi this is some text', 'and this is a linefeed.', 'and another'));

edited May 23 '17 at 12:10

Community

1
1

answered Feb 11 '11 at 18:10

Don Kirkby

53,582
27
205
286

Isn't a combination of `CARRIAGE RETURN` and `LINE FEED` by using `CHAR(13)` and `CHAR(10)` usually recommended for compatibility? `CR` is equivalent to `\r` and `LF` is equivalent to `\n` – Fr0zenFyr Apr 04 '18 at 10:38
What's wrong in CHAR(10 USING ASCII)? – Payel Senapati Oct 02 '21 at 18:31

score 39 · Answer 2 · answered May 25 '10 at 07:54

39

in an actual SQL query, you just add a newline

INSERT INTO table (text) VALUES ('hi this is some text
and this is a linefeed.
and another');

answered May 25 '10 at 07:54

chris

9,745
1
27
27

score 28 · Answer 3 · edited Mar 08 '12 at 09:11

28

For the record, I wanted to add some line breaks into existing data and I got \n to work ok...

Sample data:

Sentence. Sentence. Sentence

I did:

UPDATE table SET field = REPLACE(field, '. ', '.\r\n')

However, it also worked with just \r and just \n.

edited Mar 08 '12 at 09:11

bluish

26,356
27
122
180

answered Dec 10 '10 at 16:52

Matt

281
3
2

5

Unfortunately doesn't work if you have abbreviations in your text, like "Mr. Smith", which will be broken into 2 lines. – bluish Mar 08 '12 at 09:33
1

thanks, that was helpful for my situation where I am doing parsing and can't separate onto actual lines in the insert – salonMonsters Jul 01 '13 at 20:57
Be aware that in general you want to use only \n if in a UNIX server environment, and \r\n if in a Windows-only server environment. Do not use \r on its own. Here's a discussion on what these mean, except be aware that information abuot "Mac" using "\r" is woefully outdated - Macs have been using the standard Unix "LF" (\n) exclusively for about 15 years. -> http://stackoverflow.com/questions/1552749/difference-between-cr-lf-lf-and-cr-line-break-types – XP84 Nov 22 '16 at 17:51
1

@XP84 Good point! I would go even further and take the stance that the text in the DB should be platform-agnostic and hence use `\n` only. This is the way most such systems are dealing with it. E.g. Git. Optionally you can replace `\n` with `\r\n` upon reading on a Windows system, but mostly you'll find it's not even needed. Most Windows components will just work with only `\n`. Notepad is a notable exception to this. – Stijn de Witt Jan 07 '17 at 16:36
Wonderful! I had converted a database, and the '\n' and '\r' characters got inserted by mistake. I took care of it in a mere two statements: `UPDATE table SET field = REPLACE(field, '\\r', '\r');` and `UPDATE table SET field = REPLACE(field, '\\n', '\n');` – dhc May 31 '19 at 14:01
@dhc, you could also do that update in a single statement: `UPDATE table SET field = REPLACE(REPLACE(field, '\\r', '\r'), '\\n', '\n');` – pbarney Mar 02 '22 at 21:05

score 21 · Answer 4 · answered May 03 '12 at 14:39

21

INSERT INTO test VALUES('a line\nanother line');

\n just works fine here

answered May 03 '12 at 14:39

xtds

2,453
2
19
12

@JordanSilva and upvoters of his comment: check out [the answer by David M](http://stackoverflow.com/a/2902909/286685) it probably contains your solution. – Stijn de Witt Jan 07 '17 at 16:38
@StijndeWitt It was long ago and I don't remember how I solved the problem. But thanks for the tip. – Jordan Silva Jan 09 '17 at 07:48

starleaf1 · Answer 5 · 2017-01-19T08:30:56.903

17

MySQL can record linebreaks just fine in most cases, but the problem is, you need   tags in the actual string for your browser to show the breaks. Since you mentioned PHP, you can use the nl2br() function to convert a linebreak character ("\n") into HTML   tag.

Just use it like this:

<?php
echo nl2br("Hello, World!\n I hate you so much");
?>

Output (in HTML):

Hello, World!<br>I hate you so much

Here's a link to the manual: http://php.net/manual/en/function.nl2br.php

edited Jan 19 '17 at 08:30

answered Nov 30 '14 at 11:27

starleaf1

2,701
6
37
66

2

Note: the default `
` style is actually `
` – Brian Leishman Nov 11 '18 at 22:49

score 11 · Answer 6 · edited Apr 05 '13 at 22:27

11

INSERT INTO myTable VALUES("First line\r\nSecond line\r\nThird line");

edited Apr 05 '13 at 22:27

JSW189

6,267
11
44
72

answered Apr 05 '13 at 22:05

nancy alajarmeh

121
1
2

score 6 · Answer 7 · answered May 25 '10 at 07:53

First of all, if you want it displayed on a PHP form, the medium is HTML and so a new line will be rendered with the   tag. Check the source HTML of the page - you may possibly have the new line rendered just as a line break, in which case your problem is simply one of translating the text for output to a web browser.

Airful · Answer 8 · 2012-06-14T08:34:30.213

2

You have to replace \n with   before inset into database.

$data = str_replace("\n", " ", $data);

In this case in database table you will see   instead of new line.

e.g.

First Line
Second Line

will look like:

First Line Second Line
Another way to view data with new line. First read data from database. And then replace \n with   e.g. :

echo $data;
$data = str_replace("\n", " ", $data);
echo " " . $data;

output:

First Line Second Line

First Line
Second Line

You will find details about function str_replace() here: http://php.net/manual/en/function.str-replace.php

edited Jun 14 '12 at 08:34

answered Jun 14 '12 at 08:02

Airful

312
2
12

2

You should use PHP native function [nl2br()](http://www.php.net/manual/en/function.nl2br.php) instead of str_replace to do this. – bgondy Aug 12 '13 at 09:14
3

Agreed - you should always avoid putting HTML into your database. That should be done in your code - keep raw data in the tables. – Scott Jul 27 '15 at 19:19
Indeed. Please never include raw HTML in the text in the DB... It will become a maintenance nightmare. What are you going to do when someone uses the normal characters `<`, `>` and `&` in their text... escape it? If so, how are you going to avoid escaping the `
` you inserted? You will create a problem that cannot be solved. – Stijn de Witt Jan 07 '17 at 16:42
@Scott :: As far i know most of the rich text editor put HTML code in database. So it depends how you handle code. – Airful Jul 13 '17 at 07:33
@StijndeWitt :: Depending on situation you can easily use regular expression to detect HTML tags to avoid during escaping special characters. – Airful Jul 13 '17 at 07:34
@Airful Don't. Because you'll always leave a security breach somewhere. – Xenos Jun 08 '18 at 09:04
@Xenos you have to perform XSS on output to overcome this isseu. – Airful Jul 06 '18 at 05:32

score 2 · Answer 9 · edited Mar 08 '12 at 09:12

2

In SQL or MySQL you can use the char or chr functions to enter in an ASCII 13 for carriage return line feed, the \n equivilent. But as @David M has stated, you are most likely looking to have the HTML show this break and a br is what will work.

edited Mar 08 '12 at 09:12

bluish

26,356
27
122
180

answered May 25 '10 at 08:15

RandyMorris

1,264
9
17

1

ASCII 13 isn't `\r`? Thought `\n` was ASCII 10. – hydroiodic Apr 23 '13 at 22:31

score 0 · Answer 10 · answered Apr 12 '21 at 07:42

0

use <pre> tag instead of  in html to show your \n in database

answered Apr 12 '21 at 07:42

皮皮shrimp

35
4

score 0 · Answer 11 · answered Oct 02 '21 at 22:34

Adding to the answer given @DonKirby

INSERT INTO mytable (myfield) VALUES (CONCAT_WS(CHAR(10 using utf8), 'hi this is some text', 'and this is a linefeed.', 'and another'));

is unnecessary

The CHAR() function doesn't accept the full set of utf8 values. It accepts only ASCII values.

See - https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_char

Thus more appropriate would be to use CHAR(10 USING ASCII) in place of CHAR(10 USING utf8)

If that's the case, then why not just use `CHAR(10)`? – pbarney Mar 02 '22 at 21:08 — pbarney, Mar 02 '22 at 21:08

score -3 · Answer 12 · answered May 04 '12 at 10:34

-3

You can simply replace all \n with   tag so that when page is displayed then it breaks line.

UPDATE table SET field = REPLACE(field, '\n', '<br/>')

answered May 04 '12 at 10:34

uttam

589
2
7
33

Adding a line break in MySQL INSERT INTO text

12 Answers12

Linked

Related