Search large text field to pull out phone numbers

[SomeHelp]

I am using Crystal Reports 10.0 SP4 on a Windows 2000 desktop. The report has a string field that can have a length of 512 characters. This is a free-form text field and the telephone number can occur anywhere within this field. Since this is free-form text field, the phone numbers can be entered various ways such as 222-555-1212 or 2225551212 or 18005551212 or 1-800-555-1212 etc... I have some ideas that may work, but wanted to pick the brains of some of the more experienced developers. I know there probably isn't a perfect approach to this issue, since it is really a data problem, but I hope to get some alternative ways to address this issue. Any input would be helpful.

 

[lbass]

Are there any other numbers in the field? Is there any text that always precedes or follows the phone number?

-LB

 

[SomeHelp]

There is no text which always proceeds or follows the phone number. Most of the time there is only a phone number in the text, but there can be other numbers. Sometimes there is no phone number at all within the text. I would say 90% of the time there is a phone number within the text and no other number and the remaining 10% of the time there is no number at all or there are other numbers besides phone number.

 

[SomeHelp]

Here is solution that I am going to use:

Basic Syntax -

Formula Name: @GetPhNumber

dim intIndexA as number
dim intIndexB as number
dim intKeepLoopA as boolean
dim intKeepLoopB as boolean
Dim curChar As String
Dim lenInput As number
Dim stopPos As number
Dim intStartPos as number
Dim workingStr As String

lenInput = len({SessionTranscript.EVCONTENT})
intKeepLoopA=True
intKeepLoopB=True
intIndexA=0
intIndexB=0
'loop through the characters of text looking for numbers
do while intKeepLoopA
intIndexA = intIndexA + 1
curChar = Mid({SessionTranscript.EVCONTENT}, intIndexA, 1)

If intIndexA > lenInput Then
intkeepLoopA=False
End If

'Upon finding a number, loop through the subsequent characters looking for a non-number
if isNumeric(curChar) Then
intStartPos = intIndexA

'start of second loop
do while intKeepLoopB
intIndexB = intIndexB + 1
intIndexA = intIndexA + 1
curChar = Mid({SessionTranscript.EVCONTENT}, intIndexA, 1)

'Look for non-standard numbers, like zip codes...
if ((curChar= " ")and (intIndexB <3 or intIndexB=5)) then
intIndexB = 0
intStartPos = intIndexA
end if

'End the loops if a non-numeric character is encountered
If Not(IsNumeric(curChar) Or (curChar = "-") Or (curChar= ".") or curChar= ")" or curChar= " " or curChar= "(") Then
stopPos = intIndexB
intKeepLoopA=False
intKeepLoopB=False
end if

'End the loops if end of the text was encountered
If intIndexA > lenInput Then
stopPos=intIndexB
intkeepLoopA=False
intKeepLoopB=False
End IF
Loop

if intStartPos > 0 and intIndexB > 0 then
workingstr=Mid({SessionTranscript.EVCONTENT}, intStartPos, intIndexB)
end if
end if

Loop

workingStr = replace (workingStr," ","")
workingStr = replace (workingStr,"-","")
workingStr = replace (workingStr,")","")
workingStr = replace (workingStr,".","")
workingStr = replace (workingStr,"(","")

'Look for a 1 at the beginning
if left(workingStr,1)="1" then
workingStr=right(workingStr, len(workingStr)-1)
end if

'Look for extra numbers after the phone number
if len(workingStr) > 10 then
workingStr = left(workingStr, 10)
end if

'Look for a non-phone number
if len(workingStr) < 10 then
workingStr = ""
end if

formula = workingStr

=====================================================

Crystal Syntax -

Formula Name: @DispPhNumber


WhilePrintingRecords;

StringVar AreaCd := "(" & Left({@GetPhNumber},3) & ")";
StringVar PrefixNo := Mid({@GetPhNumber},4,3) & "-";
StringVar SuffixNo := Right({@GetPhNumber},4);
StringVar PhoneNumFmt := AreaCd & PrefixNo & SuffixNo;

PhoneNumFmt