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

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

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

Solution 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.

Solution 4

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

\n just works fine here

Solution 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

View more solutions

Comments

[bluish]

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

[hydroiodic]

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

[salonMonsters]

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

[bgondy]

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

[Scott]

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

[XP84]

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/…

[Stijn de Witt]

@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]

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

[Stijn de Witt]

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.

[Jordan Silva]

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

[Airful]

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

[Airful]

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

[Fr0zenFyr]

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

[Xenos]

@Airful Don't. Because you'll always leave a security breach somewhere.

[Airful]

@Xenos you have to perform XSS on output to overcome this isseu.

[Brian Leishman]

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

[dhc]

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

[Payel Senapati]

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

[pbarney]

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

[pbarney]

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