how do I get rid of funky characters in db table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

how do I get rid of funky characters in db table

Post by Luke »

I have a database table full of products. The client copy & pasted from word into miva (miva sucks) and miva doesn't clean the input, so I have a bunch of funky characters in my data. Could I clean this data by converting my database table to utf-8, or would that not even help at all? If it would, are there any adverse effects I should be aware of.

If this is totally silly and of course it wouldn't work, well... what would?
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

I know this might be considered kinda "low-rent", but you could always do something along the lines of a white-list search & replace under Vi/Vim (or another text editor that does regex s&r).

Maybe the following?

Code: Select all

:%s/[^A-Za-z0-9!@$%()+=-><\[\]\\\/]//g
I mean, add the appropriate symbols (and escaping) as necessary...

EDIT: I noticed a glaring error in that regex, there's probably less glaring errors left.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

If you've got a linux box, try using the 'recode' program. It'll open a file & try to re-interpret it using a character set you can specify. It might clean things up a bit.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply