List:General Discussion« Previous MessageNext Message »
From:Rob Yelvington Date:August 16 2003 8:46am
Subject:Re: querie assitance
View as plain text  
----- Original Message ----- 
From: "Paul DuBois" <paul@stripped>
To: "Rob Yelvington" <rly@stripped>; <mysql@stripped>
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


Thread
querie assitanceRob Yelvington15 Aug
  • Re: querie assitancePaul DuBois15 Aug
  • Re: querie assitanceRob Yelvington16 Aug