CLEAN( ) function

Replaces the first invalid character in a string, and all subsequent characters, with blanks.

Syntax

CLEAN(string <,extra_invalid_characters>)

Parameters

Name Type Description
string

character

 The value from which to remove default and any extra invalid characters.
extra_invalid_characters

optional

character

Invalid characters you want to remove from string in addition to the default invalid characters. You may specify more than one extra invalid character:

 " ,;\"

Tab characters, null characters, and carriage return and line feed characters are default invalid characters that are automatically removed and do not need to be specified.

To specify the double quotation mark as an extra invalid character, enclose extra_invalid_characters in single quotation marks:

 '"'

Output

Character.

Examples

Basic examples

Returns "ABC    " ("ABC" followed by four blank spaces):

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

Returns "1234.56      " ("1234.56" followed by six blank spaces):

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

Remarks

When to use CLEAN( )

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.

Specifying invalid single and double quotation marks

If you need to specify both single and double quotation marks as invalid characters, you must nest the CLEAN( ) function within itself:

CLEAN(CLEAN(string, '"'), "'")

Automatic CLEAN( )

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

[ Back to top ]

ACL Scripting Guide 14.1