CLEAN()

CLEAN() searches for any invalid characters in a string and replaces them and all subsequent characters with blanks.

Use CLEAN() to ensure that fields containing invalid data are printed correctly. You can also use CLEAN() to isolate parts of a field, such as the last name in a customer field that includes both the first and last name of the customer.

Function Format

CLEAN(C1 <,C2>)

The CLEAN() function scans a character field or expression C1 from left to right and searches for any invalid characters. If it finds one, CLEAN() replaces the invalid data and the remainder of the string with blanks.

The optional second parameter C2 allows you to specify additional character values that are to be considered invalid for the purposes of the test. You must put the values for this parameter in quotes.

You can apply the CLEAN() function automatically to all character fields by turning on the Blank Invalid Data preference. To do so, select Tools from the menu, choose Options, click the [Numeric Options] tab and check the Blank Invalid Data checkbox.

Examples

CLEAN("ABC%DEF","%") = "ABC "

CLEAN("1234.56,111,2",",") = "1234.56 "

In the following example, # represents invalid character data:

CLEAN("DOE, JOHN##102891231") = "DOE, JOHN "

CLEAN("DOE, JOHN##102891231", ",") = "DOE "