CLEAN( ) function

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


CLEAN(string <,extra_invalid_characters>)



Character. The value to remove invalid characters from.


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., ‘“’).




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.


Example Return value


“ABC    ”

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

“1234.56      ”

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