Mysql preg replace  \n

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Mysql preg replace  \n

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Mysql preg replace  \n

Post 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?
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: Mysql preg replace  \n

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Mysql preg replace  \n

Post 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.
Post Reply