Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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

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'));
                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 4, 2018 at 10:38
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');
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.
                Unfortunately doesn't work if you have abbreviations in your text, like "Mr. Smith", which will be broken into 2 lines.
– bluish
                Mar 8, 2012 at 9:33
                thanks, that was helpful for my situation where I am doing parsing and can't separate onto actual lines in the insert
– salonMonsters
                Jul 1, 2013 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. -> stackoverflow.com/questions/1552749/…
– XP84
                Nov 22, 2016 at 17:51
                @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 7, 2017 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, 2019 at 14:01
                @JordanSilva and upvoters of his comment: check out the answer by David M it probably contains your solution.
– Stijn de Witt
                Jan 7, 2017 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 9, 2017 at 7:48
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:
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
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.
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.
First Line
Second Line
will look like: 
First Line<br/>Second Line
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

                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, 2015 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 <br/> you inserted? You will create a problem that cannot be solved.
– Stijn de Witt
                Jan 7, 2017 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, 2017 at 7:33
                @StijndeWitt :: Depending on situation you can easily use regular expression to detect HTML tags to avoid during escaping special characters.
– Airful
                Jul 13, 2017 at 7: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)
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/>')
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.