query to find, then update characters in post titles & body

[gabeanderson]

I changed the encoding of my forums from ISO-8859-1 to UTF-8. As a result, I'm finding a number of undesired characters in posts, such as "�" in place of a single quote (likely because it was initially input / copied as a right single quote).

Also, the HTML code for right single quote is displaying verbatim in some places rather than as a quote.

The attached screenshot shows an example of both instances (source post).

I'd like to run a query first to find all instances where these two issues occur (in post titles and post text). Then update all.

I've done some test queries, but am not having luck getting any results back.

Suggestions on the queries?

Thanks.

[vbplusme]

Are you using phpMyAdmin to run your queries?

[gabeanderson]

I'm using the "Execute SQL Query" feature via Admin CP.

[gabeanderson]

Anyone? Anyone?

I'm just looking for something like this, but want to confirm the syntax:

select * from posts
where title like 'funky-character';

And this:

select * from posts
where body-text like 'funky-character';

Thanks!

[gabeanderson]

It doesn't seem like this should be too difficult... I'm sure I'm just missing something obvious.

Any pointers, please?

Thanks!

[Lynne]

You might want to use % in the like area (% is a wilcard). Also, the table is called "post" and the body-text is stored in a field called "pagetext". So, something more like:

PHP Code:

select * from post
where pagetext like '%funky-character%' 


That will find the posts with the funky_character in them. (I think - I am no mysql guru!) Also, if you have a table prefix, you will have to put that in front of "post".

[gabeanderson]

Thanks, Lynne! That did it for basic searches. But I still can't seem to get my funky characters (like �) to show up in query results by pasting it in.

That's how some characters show up in query results, but I guess they're encoded in the database some other way...

Example query results for this post is attached.

I know that character was originally "»" (and have since removed that from the page title of links like it), but trying a query like the below yields no results:

select * from post
where pagetext like '%raquo%'

Any other ideas?

Thanks!

[Lynne]

If you know it's in that exact post, have you tried looking at the pagetext for that postid and seeing exactly what is written in there?

Also, are you using page caching? If so, you'll want to turn that off (set cached posts lifespan to 0) for now.

[gabeanderson]

Hi Lynne- The above screenshot does show exactly what's in the pagetext for the post - for this example... but the point is that this character may exist in potentially hundreds of other posts since I changed the encoding to UTF-8, not just in this one.

So the problem is how I identify those posts since running the query to find the "�" character is not working.

[Lynne]

So does "�" show up in posttext or does "»" show up in the posttext (I'm talking about exactly what is in the field in the database)? Like if you did a select posttext from post where postid=50736, what do you get in there?