Finding Telephone Numbers 2

[simoncpage]

Hi

I have a database set up with text fields for phone number inputs. The problem I have is that they are often inputed with spaces.

So
(1) 0123 0123 4567 or
(2) 0123 01234567 or
(3) 012301234567 or
(4) 01234567

could be an example I wish to do a search of parts of this (567) so that if the phone number is like the last example (4).

How can I convert this to a format so that I can search for parts of it as a number?

I have tried texttonum(phone) but this 1. loses the 0 and secondly wont find 567 in (4)

Any help would be great, thanks

Simon

 

[Schlogg]

A. Is the 'piece' you are looking for always the same? i.e last three characters? If so, you could set up a calculation field to extract that and do your find on the calculation field.

Otherwise, define a field
PhoneNum = Substitute(PhoneNo In, " ", "&quot
This gets rid of the blanks but still leaves you with variable length numbers.
B.
1. set up a separate text field - NumSearch
2. Replace NumSearch, Right(PhoneNum, 3) OR
Replace NumSearch , Middle(PhoneNum, 9, 3)

C. Set up three fields -
Start Number
Count Number
NumSearch Calculation = Middle(PhoneNum, Start, Count)
You then set Start to the point in PhoneNum where you want to start looking, and Count to the number of characters. Perform the Find on NumSearch.
This will find your first three but not no. 4. You might need to do an extra find with a different Start value and 'extend found set'.

There are many other ways of doing your Find but it really depends on how your Find criteria will vary.




Cheers,
Paul J.

 

[simoncpage]

Thanks Paul

This is fine for doing the search for the end but I'm really looking for a solution to do searches within numbers too

0123456781234

so if I did a search for 567 I would get this record - but bare in mind that I do not know where it appears in the number

I didnt want to go down the route of creating a calc field which contains the number broken down into a number of different lengths as for 40k+ records this isnt ideal

Any other ideas?

Thanks

Simon

 

[Schlogg]

Simon,
I don't think you have any choice but using an intermediate field - not zillions of them.
Try this.
Define StringHold as text. Use it to hold your '567', or whatever, string.
Define NumSearch (Calculation) =
PatternCount(PhoneNum, StringHold)
Then Find on NumSearch > 0
The reason for using StringHold is to make it simple to enter the string rather than re-defining NumSearch all the time.





Cheers,
Paul J.

 

[Philland]

Maybe I'm missing something here, but wouldn't the best long term solution be to create three separate telephone number fields so that the phone numbers can be segmented like your example 1 in all cases.

This way your input is standardized so your searches become simpler.

The fields would set up as three separate 4 character fields (or whatever meets your needs) ____ - ____ - ____ and by using set calc result(right(text, #)) steps inside a loop you can quickly parse your existing numbers into these fields. Although the records may need to be parsed in groups (1, then 2, then 3, then 4) to accommodate the inconsistent input.

Phil

 

[Schlogg]

Simon,
If you need to find the string anywhere in the phone number which might be (virtually) any length, try this -
Define SearchString Global Text
Define SearchNum calculation =
PatternCount(PhoneNum, SearchString)

Find on SearchNum > 0


Cheers,
Paul J.