-1

i have a mysql database with a table post_text. In posttext i can see the post message. For example:

Goodmorning everybody,
so I want to ask you something about...

I want to replace: "Goodmorning everybody, --linebreak-- so I want" with "Jessica wants"

The result is

Jessica wants to ask you something about...

The problem is the linebreak, i cant see \n or \r in phpmyadmin so how to search for exact match sentence linebreak sentence and the replace it with new sentence.

Thanks very much!

u_mulder
  • 54,101
  • 5
  • 48
  • 64
user3069797
  • 1
  • 3
  • people do make things difficult for themselves. If its just one or two rows you want to amend. Using phpMyAdmin, browser to the row, Click the Edit link and you are offered an edit page where you can manually edit this column. Put in whatever you like. _This is why you have phpMyAdmin_ – RiggsFolly Jan 26 '17 at 14:38
  • Its 10.000 lines ;) – user3069797 Jan 26 '17 at 14:41
  • Ah.... then you should look at Lucas's answer. But are you sure that you want to change all those rows to say the same thing? – RiggsFolly Jan 26 '17 at 14:43

2 Answers2

0
UPDATE yourTable SET post_text = REPLACE(REPLACE(post_text, '\r', ''), '\n', '');

It works for me. Hope it helps!

Source: How to remove new line characters from data rows in mysql?

u_mulder
  • 54,101
  • 5
  • 48
  • 64
Lucas Palaian
  • 374
  • 2
  • 12
0

As you marked this as PHP, and PhpMyAdmin I'm not sure which approach you are going for, but I'll provide both.

In PHP you can replace text in a string using the str_replace() function.

You can combine this with the "chr()" function to use specific ASCII codes to find text.

An example of replacing all "carriage return (\r)" characters to the html string "
" would be:

str_replace(char(13), '<br />', $my_post_text_data_cell);

*Note: That unix and windows use different new line characters, some are simply \r others are \r\n

For PHPMyAdmin, you are going to have a difficult time using the browser to search, but you could craft a SQL statement to find them, when using the character symbols, you'll want to use a regular expression check.

Take a look at: https://dev.mysql.com/doc/refman/5.7/en/regexp.html

As described, your SQL would look something like: (Untested)

SELECT * FROM post_text WHERE post_data REGEXP '^\r';

You can find more information about using regular expressions here: https://www.tutorialspoint.com/mysql/mysql-regexps.htm

Danoweb
  • 423
  • 2
  • 9
  • Can i say SELECT * FROM post_text WHERE post_data REGEXP '^Goodmorning everybody\r'; – user3069797 Jan 26 '17 at 15:12
  • Can i use your mysql code with a part of a sentence. So.. Goodmorning everybody;\r? – user3069797 Jan 26 '17 at 15:16
  • You can, however not in the form you have it. Regular Expressions use a specific (and not intuitive!) syntax. I would recommend using a regular expression builder like the one here, to help build out the exact expression you require: http://regexr.com/ You can use the chart on the second link in my answer above to understand the Syntax required – Danoweb Jan 26 '17 at 15:43