Illegal characters to be removed 1

[Schnappa]

Hi all

I have a VB function that I have found, and would like to use it in Crystal. The following is an extract of the formula I want to use to identify phone numbers with an illegal character set (ie - not those listed in the second line). This function was sent by a friend, and of course, the 'RemoveIllegalChars' wont work in Crystal (I am using XI).

Phone Number = trim("Work 1234 5678")
Phone Number = RemoveIllegalChars(OldNumber,"0123456789 +")
If OldNumber <> NewNumber then "Its got illegal chars"

Is anyone able to help out with an alternative.

Cheers

Gez

 

[lbass]

I can't tell how exactly this function would work. It would be easier if you showed samples of the variation in your data, and then how you would like the phone number to look.

-LB

 

[Schnappa]

Thanks for that....

The database is set up with the phone numbers field as a string with a length of 50 - so not unusually, it's a free for all around data quality.

I need to identify would include any phone number with an alphanumeric (exluding "(", ")", "+" or a space within the text). I will be trimming the fields to clear any spaces prior or post the numerics.

I.e.

HOME(09)321 3213 - I need to identify the 'HOME'
(09)321 3213H - I need to identify the 'H'

etc etc.

Cheers

GV

 

[lbass]

Try:

numbervar x;
numbervar y := len({@phoneno});
//substitute your {table.field} for {@phoneno}
stringvar z := "";

for x := 1 to y do(
if isnumeric({@phoneno}[x]) or
{@phoneno}[x] in ["(",")","+"," "]
then z := z + {@phoneno}[x] else z := z);
trim(z)

This assumes that you want you want to leave in: "(",")","+"," ". The formula does the trim for you in the final line: trim(z).

-LB