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!!

Conformable

Find answer here https://mcmap.net/q/205526/-how-to-save-a-text-string-with-line-breaks-to-a-mysql-database-text-field-using-php – Unkindly

Answer 1

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'));

Stanislas

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 – Roark

What's wrong in CHAR(10 USING ASCII)? – Archenemy

Answer 2

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');

Making

Answer 3

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.

Blackstone

Unfortunately doesn't work if you have abbreviations in your text, like "Mr. Smith", which will be broken into 2 lines. – Apologete

thanks, that was helpful for my situation where I am doing parsing and can't separate onto actual lines in the insert – Adolphadolphe

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. -> #1553249 – Slightly

@Slightly 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. – Curlew

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'); – Teague

@dhc, you could also do that update in a single statement: UPDATE table SET field = REPLACE(REPLACE(field, '\\r', '\r'), '\\n', '\n'); – Crab

Answer 4

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

\n just works fine here

Franctireur

@JordanSilva and upvoters of his comment: check out the answer by David M it probably contains your solution. – Curlew

@StijndeWitt It was long ago and I don't remember how I solved the problem. But thanks for the tip. – Supernatural

Answer 5

MySQL can record linebreaks just fine in most cases, but the problem is, you need <br /> 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 <br /> 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

Framing

Note: the default <br> style is actually <br /> – Samaveda

Answer 6

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

Sorites

Answer 7

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 <br /> 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.

Ulm

Answer 8

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.

Sacramental

ASCII 13 isn't \r? Thought \n was ASCII 10. – Uretic

Answer 9

1. You have to replace \n with <br/> before inset into database.

   $data = str_replace("\n", "<br/>", $data);

   In this case in database table you will see <br/> instead of new line.

   e.g.

   First Line
Second Line

   will look like:

   First Line<br/>Second Line

2. Another way to view data with new line. First read data from database. And then replace \n with <br/> e.g. :

   echo $data;
$data = str_replace("\n", "<br/>", $data);
echo "<br/><br/>" . $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

Sunda

You should use PHP native function nl2br() instead of str_replace to do this. – Cacophonous

Agreed - you should always avoid putting HTML into your database. That should be done in your code - keep raw data in the tables. – Pliny

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 <br/> you inserted? You will create a problem that cannot be solved. – Curlew

@Pliny :: As far i know most of the rich text editor put HTML code in database. So it depends how you handle code. – Sunda

@StijndeWitt :: Depending on situation you can easily use regular expression to detect HTML tags to avoid during escaping special characters. – Sunda

@Sunda Don't. Because you'll always leave a security breach somewhere. – Simard

@Simard you have to perform XSS on output to overcome this isseu. – Sunda

Answer 10

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

Bolingbroke

Answer 11

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)

Archenemy

If that's the case, then why not just use CHAR(10)? – Crab

Answer 12

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

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

Midships