Social Security Error

[Nunina]

Hi!!!

I'm having the following problem:

Tabla A & Table B have the social security field as the joining field of the tables. The thing is that the social security can appear as 123-45-6789 or 123456789 on either table.

Obviously, when I try to join table A & B, it cannot find the records since maybe in table A is 123-45-6789 but in table B is 123456789. It is the same customer, but since the format of the social security is not the same, it cannot join the tables.

Does anyone have any idea how I can "convert" or format this field so that the query reads it as equal without having to update the table? It is a production table that is filled automatically when data is entered into the parent table.

Thanks in advance.

Nunina

P. S. - It can appear either way on either table. In some cases, I have two records for the same customer (on the same table) only because the social security field is not the same length. (lenth(123-45-6789) = 11; len(123456789) = 9)

 

[carp]

Try

WHERE REPLACE(a.ssn,'-','') = REPLACE(b.ssn,'-','')

Note that '' is two single ticks with no space between them.

 

[Nunina]

Thanks a lot!!! This really helped!

Nunina