CLEAN( ) function

Returns a string where the first invalid character, and all subsequent characters, are replaced with blanks.

Syntax

CLEAN(string <,extra_invalid_characters>)

Parameters

string

Character. The value to remove invalid characters from.

extra_invalid_characters

Optional. Character. Specifies invalid characters you want to remove from string in addition to the invalid characters that are removed automatically (tab characters, null characters, and carriage return and line feed characters). This parameter must be must specified using a quoted string. If you are specifying the double quotation mark character as an extra invalid character, enclose it in single quotes (i.e., ‘“’).

Output

Character.

Remarks

Use this function to ensure that fields containing invalid data are printed correctly. You can also use this function 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.

You can apply the CLEAN( ) function automatically to all character fields by adding SET CLEAN ON to your script. You cannot specify extra individual characters using this option.

Examples

Example Return value

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

“ABC    ”

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

“1234.56      ”



(C) 2012 ACL Services Ltd. All Rights Reserved.