How do i find line breaks in mysql?

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

Alexis Wilke

17.7k10 gold badges77 silver badges134 bronze badges

asked May 25, 2010 at 7:50

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

answered Feb 11, 2011 at 18:10

Don KirkbyDon Kirkby

49.6k25 gold badges197 silver badges276 bronze badges

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

answered May 25, 2010 at 7:54

chrischris

9,4351 gold badge25 silver badges26 bronze badges

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.

bluish

25.2k26 gold badges116 silver badges175 bronze badges

answered Dec 10, 2010 at 16:52

MattMatt

2713 silver badges2 bronze badges

6

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

\n just works fine here

answered May 3, 2012 at 14:39

xtdsxtds

2,3932 gold badges18 silver badges12 bronze badges

2

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:

Output (in HTML):

Hello, World!
I hate you so much

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

answered Nov 30, 2014 at 11:27

starleaf1starleaf1

2,4426 gold badges39 silver badges61 bronze badges

1

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

JSW189

6,18711 gold badges42 silver badges71 bronze badges

answered Apr 5, 2013 at 22:05

0

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.

answered May 25, 2010 at 7:53

David MDavid M

70.3k13 gold badges154 silver badges184 bronze badges

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.

bluish

25.2k26 gold badges116 silver badges175 bronze badges

answered May 25, 2010 at 8:15

RandyMorrisRandyMorris

1,2649 silver badges17 bronze badges

1

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

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

answered Jun 14, 2012 at 8:02

AirfulAirful

3102 silver badges12 bronze badges

7

use

 tag instead of 
 in html to show your \n in database
 answered Apr 12, 2021 at 7:42
 
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)
 answered Oct 2,
2021 at 22:34
 
Payel SenapatiPayel Senapati
8961 gold badge10 silver badges22 bronze badges
1
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', '
')
 answered May 4, 2012 at 10:34
 
uttamuttam
5892 gold badges7 silver badges32 bronze badges
 

How do I find line breaks in SQL?
We might require inserting a carriage return or line break while working with the string data. In SQL Server, we can use the CHAR function with ASCII number code..
 
Char(10) – New Line / Line Break..
 
Char(13) – Carriage Return..
 
Char(9) – Tab..
 
What is line break in MySQL?
A few other ways we can insert a line break in SQL are with MySQL and SQL Server. MySQL. -- Using both \r\n SELECT 'First line. \r\nSecond Line. ' AS 'New Line'; -- Using both \n SELECT 'First line.
How do I escape a new line in MySQL?
MySQL recognizes the escape sequences shown in Table 9.1, “Special Character Escape Sequences”.
...
Table 9.1 Special Character Escape Sequences..
 
How do I find a row in MySQL?
MySQL SELECT statement is used to retrieve rows from one or more tables. The statement can also include UNION statements and subqueries. SELECT statement is used to fetch rows or records from one or more tables.