[SOLVED] Find and Replace (by column)

[pwnuspoints]

Hey phpfreaks,

 

I have a special offer for you- just send 50 payments of $5.99 to --- lol.. just kidding.

 

I appreciate any help you can give me on this topic. Let me explain the scenario.

 

I maintain a table used as a staff directory for my website. It lists things like first name, last name, e-mail, phone numbers and so on. I am attempting to transition this table from being a static archive to the table I use to store username and password information for each employee. In this way I will end up with two separate tables without having to re-enter duplicate data manually or distort the original table. With me so far?

 

My concern centers around the e-mail address column. For sake of simplicity I would like to use the first part of every e-mail address as usernames. I will also create a column containing a universal password (which can be changed after logging in). So I've got a column with e-mail addresses listed as strings "[email protected]". Using HeidiSQL I know I can duplicate the entire e-mail column. From there my next instinct would be to find and replace all "@domain.com" with an empty string "". That way I would have one column still listing their e-mail addresses and another listing their log-in username.

 

I realize there are several ways to approach this.. I could probably make a php processing script to do the work.. or I could just leave the e-mail column as is and use a trim function on log-in to verify the username. However.. for the sake of my own sanity and work-flow. I'd like to end up with the two columns as detailed above.

 

I'm using my best infomercial voice when I say: There's got to be a better way. *camera zoom*

 

Any ideas?

 

Thanks Ladies and Gents!

[pwnuspoints]

does this mean I have to modify those cells line by line? because if it does.. I'm friggin' quitting my job... No, probably not-- but I'll complain a lot.

[pwnuspoints]

I figured it out on my own.

 

Using Heidi SQL I exported the data as a CVS document. Then I used Microsoft Excel 2007 to import the CVS document.. in excel it is rather a simple task to duplicate a column. Then, using find and replace I replaced "@domain.com" with an empty string. However to maintain the integrity of the e-mail column I could not use the "replace all" button. I had to click replace 800 times.. but hey, better than spending 8 hours re-typing that mess.

 

Anyway~ thought I'd put that out there in case it helps anyone else.

 

OH YEAH - I guess I exported the data back out as a cvs.. then imported it back into Heidi SQL on my server. Lickety-split. How do you like that?