The more you can tell us about the data, the simpler it will be for someone
to assist.
Usually it's best to start with "I've tried ... and the results I get are
....". And "but I keep getting errors" is neither very specific nor very
helpful.
In your instance, some pertinent questions would be (1) Is this text data?
(2) Is the "###-###-####" always embedded or may it be at the beginning or
end? (3) If embedded is it always preceded by a blank or space? (4) Always
followed by blank or space? (4) Can there be other hyphens in the field? (5)
Can there be more than one sequence of this format to be extracted? (6) Can
there be another sequence of this format that shouldn't be extracted? If
so, how can you determine which is to be and which is not to be extracted?
A case of this is local phone numbers (i.e. without area code)
and Thompson Rivers University student numbers (the old style: I
believe they may be changing this) which are both
###-####
And, someone who digs into the problem may come up with more questions.
And even without digging in.
7) Is there another similar format? The format might be for phone
numbers. (OP, you did not specify.) Phone numbers can also be
expressed as
(###) ###-####
(###)###-####
###-####
Then there is the leading "1" for long distance. The previous is in
the North American Numbering Plan. All bets are off if you try to
handle all countries' phone numbers.
8) Are the hyphens always used? If not, are they deleted or each
replaced with a space? (This happens with credit card numbers and
Canadian Social Insurance Numbers.)
Note: This point is relevant with point 6 made by "Access
Developer". Canadian Social Insurance Numbers and U.S. Social
Security Numbers are both nine digits with two hyphens, but the
formats are
###-###-###
and
###-##-####
respectively.
9) If the sequence is one that has validation rules attached to it,
will you extract all values or only valid ones?
Note: This point with point 8 considered may bite you. A
Canadian SIN (abc-def-ghi) is validated by
(a + c + e + g + i + sum of digits(bdfh*2)) mod 10 = 0
I do not know a rule for the U.S. SSN.
Sincerely,
Gene Wirchenko
