From: Peter Brawley
Date: October 30 2007 1:03am
Subject: Re: Replace on Join
List-Archive: http://lists.mysql.com/mysql/209775
Message-Id: <472682F6.2040103@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------070603040408050005020706"

--------------070603040408050005020706
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

How about this ...

update schedule s
join directory d on s.email = replace(d.email, '@wnc.edu', '@wncc.edu')
set s.email=replace(s.email, '@wncc.edu', '@wnc.edu' );

PB

-----

Kevin Murphy wrote:
> I'm trying to do a join on two pieces of data that are not quite 
> exactly the same. Basic story, I have 2 tables (output from 2 
> mainframes) in MySQL where one table has our email addresses as 
> @wncc.edu (our old name) and the other has it as @wnc.edu (our new 
> name). So here is an abbreviated version of the query that is trying 
> to match them, and it doesn't work. Any suggestions?
>
> SELECT * FROM schedule
> LEFT JOIN directory ON
> (    REPLACE('@wncc.edu','@wnc.edu',schedule.email) = 
> directory.email    )
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition. 
> Version: 7.5.503 / Virus Database: 269.15.11/1094 - Release Date: 10/26/2007 8:50 AM
>   

--------------070603040408050005020706--