I strip non-numeric numbers out of text fields that contain phone numbers. This method should work for SSN's as well:
I'm using Filemaker Pro 6.0 (I know it's old, but it still works great!) I don't see a getas. This is what I have so far, I just need to strip out the hyphens, if there's a social in the field
Left(Social Security # & Spaces,9)
They changed the name of the function somewhere along the way. In FMP 5.5 they called the function: "TextToNum". I suspect it has that name in FMP 6.
There are text functions such as Mid and Position that you can use to search for and delete hyphens, but the expression is much more complicated.
Come to think of it, Substitute(textfield,"-","" ) will strip out the hyphens as well.
Set Field [SSN ; Abs(SSN) ]
If you want to remove hyphens, then remove hyphens by using the Substitute function. Beware of "cute tricks" that convert the data type, because these will also remove any leading zeros the "number" might have.
Careful, folks. Remember that some folks (I believe it's New England folks) have SSN's that begin in a zero. If you convert the field to a number, you lose that leading zero.
I haven't worked in FMP 6 in a long time (and don't have a copy to look at), but you're looking for something that will either allow you to "substitute" an empty string ("") for a hyphen, or something that limits the allowable characters to 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9.
[3 minutes too slow in my typing.]
Good point. Since phone numbers don't start with leading zeroes, but SSN's might. I hadn't considered that possibility.
With all respect to Comment, using the type conversion approach has it's advantages in that all non-numeric characters are stripped out, thus spaces and typographical errors are removed as well.
If you want to use GetasNumber or Texttonum and avoid the leading zero issue, use:
Right("000000000" & getasnumber(Ssntable::ssnfield),9)
I'm not sure if it goes all the way back to FM6, but Filter () should be the best approach. Limit it to the characters 0-9, and you're good to go. No worries about other non-numeric values.
Thank you to everyone that responded. You're all so helpful. With all the wonderful suggestions I was able to come up with the following calculation and it seems to be working beautifully.
Left(Substitute(Social Security,"-","" ) & Spaces,9)
Thanks again. Have a great evening!