Filtering Non Alphanumeric Characters

[lesleycampbell]

I have name and address fields that should only contain alphanumeric characters. I want the report to show only the employees' records that contact non alphanumeric characters so that they can be corrected. I need help on how to restrict the records to only show these. Thank you!!

 

[pmax9999]

I am not convinced this is the best way to do it, but it is the only way I can come up with.

Any approach is likely to be slow because of the need to look at every character in every address record, however in my (limited) testing on a very small data set (in excel) it did work.

Create a record selection formula as follows:

[Code Record_Selection]
WhileReadingRecords;

Local StringVar ADD := {Table.ADDRESS};
Local NumberVar LN := Len({Table.ADDRESS});
Local NumberVar i := 1;
Local BooleanVar Result := FALSE;


For i := 1 to LN Do

If Not (
AscW(ADD) = 32 or //check for space
AscW(ADD) in [10 to 13] or //check for lower case
AscW(ADD) in [48 to 57] or //check for numeric
AscW(ADD) in [65 to 90] or //check for Upper case
AscW(ADD) in [97 to 122] //check for line feed, carriage return
)
Then Result := TRUE
Else Result := Result;

Result = TRUE
[/Code]

It will return all records where the {Table.ADDRESS} column has anything other than Uppercase or Lowercase alpha characters, numbers, spaces or carriage return/line feed type characters. Not sure if full stops, commas etc are acceptable - if so you will need to allow for those too.

Hope this helps.

Cheers
Pete

 

[pmax9999]

The efficiency of this could be improved a little, as once a non-complying character has been found there is no need to continue analysing the address string. This could be achieved by amending the Record Selection slightly as follows to exit the loop as soon as one problem character is found:

WhileReadingRecords;

Local StringVar ADD := {Table.ADDRESS};
Local NumberVar LN := Len({Table.ADDRESS});
Local NumberVar i := 1;
Local BooleanVar Result := FALSE;


For i := 1 to LN Do

        If  Not (
                AscW(ADD[i]) = 32 or            //check for space
                AscW(ADD[i]) in [10 to 13] or   //check for lower case
                AscW(ADD[i]) in [48 to 57] or   //check for numeric
                AscW(ADD[i]) in [65 to 90] or   //check for Upper case 
                AscW(ADD[i]) in [97 to 122]     //check for line feed, carriage return
                )
        Then    (
                Result := TRUE;
                Exit For
                )
        Else    Result := Result;

Result = TRUE

Pete

 

[lesleycampbell]

Thank you both so much!! The second option worked the best!

 

[AndyMc]

I'm with you - Pete's solution was much better than Pete's solution

 

[lesleycampbell]

Yeah, you know what I meant. LOL

 

[pmax9999]

Both of me is (are?) just happy it helped.

Pete