Page 1 of 1

Mysql preg replace  \n

Posted: Fri Nov 28, 2014 3:16 pm
by cjkeane
Hi everyone,

i have  appearing in my db after \n. is there a a way to either run a query such as

Code: Select all

update 'content` set `TextField` = replace(`TextField` ,'Â \n','');
or using preg_replace?

thanks.

Re: Mysql preg replace  \n

Posted: Fri Nov 28, 2014 6:29 pm
by requinix
The problem is that your character encodings are wrong. Simply removing the Âs will not fix the problem.

What does

Code: Select all

SHOW CREATE TABLE content;
show? And are there other tables that have the same problem?

Re: Mysql preg replace  \n

Posted: Fri Nov 28, 2014 7:08 pm
by cjkeane
requinix wrote:The problem is that your character encodings are wrong. Simply removing the Âs will not fix the problem.

What does

Code: Select all

SHOW CREATE TABLE content;
show? And are there other tables that have the same problem?
there is only one table with this problem, but there are 140,000 ish records in the table.
i know its a character encoding issue. Data was imported from another db format so not all data is utf8.
if i use utf8_decode, it will fix some issue, but then cause others. if i use utf8_encode, that also causes issues.
if i don't use either, some content is displayed correctly, but as i said, there is the odd anomaly, such as the one I've mentioned here.

so what would your suggestion be to resolve the issue of  appearing before any newline

Re: Mysql preg replace  \n

Posted: Sat Nov 29, 2014 2:16 am
by requinix
You mean  appearing before a space? That's no ordinary space - that's a non-breaking space. In UTF-8 it takes two characters to represent and the first is, yes, a Â.

Change the encoding of that column to binary, and then change it to one of the utf8_* charsets. If you went directly from latin1 (which I'm going to assume you're using now since you didn't post that CREATE TABLE I asked for) to utf8 then MySQL would convert all the data for you... but you don't want that because the data is fine and it's merely the charset that's wrong. Converting to binary and then to utf8 won't change the data.