Comparing Names [with space and without space] 1

[acct98]

I have two tables:
TABLE A TABLE B
SSN 123456789 SSN 123456789
NAME DOE, JOHN VNAME DOE,JOHN

I would like to seleced all records from both tables where the SSN & NAME are the same. However, in the example above there is a space between the last name and first name in TABLE A and no space between the last name and first name in TABLE B. Is there a simple way to compare the names?

 

[SantaMufasa]

Acct,

You have multiple options available to you to resolve your need. One method is to get rid of just a blank space that follows a comma:

select 'These are equal' from dual
where replace('DOE, JOHN',', ',',') = replace ('DOE,JOHN',', ',',');

'THESEAREEQUAL'
---------------
These are equal

...or eliminate all blanks spaces altogether:

select 'These are equal' from dual
where translate('DOE, JOHN','^ ','^') = translate('DOE,JOHN','^ ','^')

'THESEAREEQUAL'
---------------
These are equal

Let us know if either of these methods please you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:46 (26Jul04) UTC (aka "GMT" and "Zulu"), 11:46 (26Jul04) Mountain Time)