Changing SSN Filed to match correct format

Hi All
I asked this about a month ago and got a couple of answers but both were a
bit over my head so I am taking another shot, explaining it a bit more in
case I was not clear.
I inherited a database that did not put an input mask format in the ssn
field. The data entry people can now enter a persons ssn in any manner they
choose (123-45-6789, 123456789, abc123456, abc-12-3456, etc). Because I do
compare to other database sources that have it correctly formatted I get bad
matches (or worse no matches) because of the format. I need a way to
reformat the SSNum field so it is correctly formatted in the ###-##-####
manner (#'s in this case meaning alphanumeric, the dashes just need to be
there).
Any ideas?OK. I have it this far - -
If I use this formula:
SELECT LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum, 4)
as SSN, Clients.First_Name + ' ' + Clients.Last_Name
FROM Clients
It recodes the SSN as I need it. How do I get this result to the UPDATE
function inside the main db?
Possibly:
Update Clients
Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum, 4)
'
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:8AE9A112-DA51-474C-B1C7-8AB34229A813@.microsoft.com...
> Hi All
> I asked this about a month ago and got a couple of answers but both were a
> bit over my head so I am taking another shot, explaining it a bit more in
> case I was not clear.
> I inherited a database that did not put an input mask format in the ssn
> field. The data entry people can now enter a persons ssn in any manner
> they choose (123-45-6789, 123456789, abc123456, abc-12-3456, etc). Because
> I do compare to other database sources that have it correctly formatted I
> get bad matches (or worse no matches) because of the format. I need a way
> to reformat the SSNum field so it is correctly formatted in the
> ###-##-#### manner (#'s in this case meaning alphanumeric, the dashes just
> need to be there).
> Any ideas?