----- Original Message ----- From: "Paul DuBois" <[EMAIL PROTECTED]> To: "Rob Yelvington" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 15, 2003 4:10 PM Subject: Re: querie assitance
> At 15:52 -0500 8/15/03, Rob Yelvington wrote: > >I need some help with a query. > > > >I have two tables in one data base that both have a SSN field. The ssn > >field in one table contains slashes and the ssn field in the other does not. > > > >I know how to use CONCAT() and RIGHT(),MID(), and LEFT() on one of the ssn > >fields to obtain a result with digits only. > > > >But, I need to obtain a result set that includes a field from each table > >matched by ssn with one field from each. > > > >For example, let's say that table 'one' looks like this: > >'111/22/3333','John Q Public' > > > >Table 'two' looks like this: > >'11122333','somecode' > > > >What I'd like to achieve is a result set of: > >'111223333','John Q Public','somecode' > > > >I guess what's throughing me off is using CONCAT() with the other substring > >items. > > > >Can this be done with one query? > > > >Mucho appreciation for any advice or assistance. > > > >Thanks! > > > >~Rob > > For the table that has the field with dashes, sounds like you want to > use REPLACE(ssn,'/','') to remove the slashes. That'd probably be simpler > than what it sounds like you're doing now. > > Without seeing your original query, it's difficult to know for sure, but > I'd guess you'll want to do something like this: > > SELECT whatever-fields-you-want FROM t1, t2 > WHERE t1.ssn = REPLACE(t2.ssn,'/','') ... > > > -- > Paul DuBois, Senior Technical Writer That's exactly what the issue was, replace() being much simpler than what I was originally trying to do...I haven't used replace() before...have read about it and just shoved it to the back of line! The final query ended up looking like this: mysql> select a,b,table1.c from table1,table2 where table1.c = replace(table2.c,'/',''); Thanks so much, Mr. Dubois! Respectfully yours, ~Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]