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 Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
