如何从 mysql 中的数据行删除新的行字符？

小开

最佳答案

your syntax is wrong:

update mytable SET title = TRIM(TRAILING '\n' FROM title)

Addition:

If the newline character is at the start of the field:

update mytable SET title = TRIM(LEADING '\n' FROM title)

小开

UPDATE mytable SET title=TRIM(REPLACE(REPLACE(title, "\n", ""), "\t", ""));

小开

UPDATE test SET log = REPLACE(REPLACE(log, '\r', ''), '\n', '');

worked for me.

while its similar, it'll also get rid of \r\n

http://lists.mysql.com/mysql/182689

小开

1) Replace all new line and tab characters with spaces.

2) Remove all leading and trailing spaces.

 UPDATE mytable SET `title` = TRIM(REPLACE(REPLACE(REPLACE(`title`, '\n', ' '), '\r', ' '), '\t', ' '));

小开

update mytable set title=trim(replace(REPLACE(title,CHAR(13),''),CHAR(10),''));

Above is working for fine.

小开

Removes trailing returns when importing from Excel. When you execute this, you may receive an error that there is no WHERE; ignore and execute.

UPDATE table_name SET col_name = TRIM(TRAILING '\r' FROM col_name)

小开

My 2 cents.

To get rid of my \n's I needed to do a \\n. Hope that helps someone.

update mytable SET title = TRIM(TRAILING '\\n' FROM title)

小开

For new line characters

UPDATE table_name SET field_name = TRIM(TRAILING '\n' FROM field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\r' FROM field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\r\n' FROM field_name);

For all white space characters

UPDATE table_name SET field_name = TRIM(field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\n' FROM field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\r' FROM field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\r\n' FROM field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\t' FROM field_name);