MySQL Column Find Replace

[Maljonic]

I've been trying all day to figure out different ways of getting rid of spaces or words etc from single columns. I know there must be an easy way to do it but I always try everything I can think of first before I ask anyone else, which I s why it took me all day to get around to writing this.

Anyway so I have a table names DOGS and in that table I have a column named BREEDS and I want to remove all the spaces between the words, say 'Springer Spaniel' want to be 'SpringerSpaniel'.

I started out chopping up the table, saving it, uploading bits of it and all sorts. I have Microsoft Excel 2007 but I can't figure out what format I need to choose for exporting from phpmyadmin and how to save it in the right format in Excel 2007 to upload it again - even though I've done this before by some fluke in the past.

But anyway I found this code which should find and replace text using quiries in phpmyadmin, only I can't get that to work either:

update tablename set tablefield= replace (tablefield, “findstring”,”replacestring”) ;

Is that the right code for changing stuff in a single column? If it is can you explain for a MySQL dummy how I would use it by writing out exactly where stuff goes for say a table called DOGS in a column called COLOR and I want to replace all instances of 'Shit' with 'Poo'?

 

[Chilly]

Easy, you even had the correct function.

SELECT `BREEDS`. replace(BREEDS, ' ', '') from DOGS

and the full update:

update `DOGS` set BREED = REPLACE(BREED, ' ', '');

(and I tested those on a quick table I knocked up with the kind of data you have - eg "sdas asda asd asd" etc.)

 

[Maljonic]

Thanks, that's great! I love this stuff when it works, it's kind of like magic when you consider how much time it saves - well maybe nerdromancy.