REMOVE( ) function

Returns a string with all characters removed, except for a specified list of allowed characters.


REMOVE(string, valid_characters)



Character. The string to remove characters from.


Character. The characters that are to be retained from the string parameter.





This function has been superseded by the functions INCLUDE( )and EXCLUDE( ). It is still available in the current version for backwards compatibility with earlier versions of ACL.

The REMOVE( ) function removes unwanted characters from character data and returns a fixed length string. If you are specifying double quotes in valid_characters, enclose them in single quotes (i.e., ‘”’). The function is case-sensitive.

You can use this function to normalize data fields that do not have a consistent format, such as address fields. You can also use this function to remove punctuation or other invalid information from poorly edited fields. The REMOVE( ) function can also be used to clean data in fields before using the SORT or JOIN commands, for duplicate matching, or for report output.

This function is similar to the INCLUDE( ) function except that REMOVE( ) adds blanks to the end of the output to replace the characters that have been removed. The original length of string is retained.

This function is also different from the CLEAN( ) function because it moves any valid characters to the left side of the field, retaining their order and adding blanks to the end result so the length is the same as string, while the CLEAN( ) function fills the remainder of a field with blanks.


Example Return value

REMOVE("ABC 123 XX4","ABC123")

“ABC123     “

REMOVE("zABC 123 XX4","ABCX123")

“ABC123XX   “

REMOVE("ABC 123 XX4", "1234567890")

“1234       “

To create a field for sorting or matching that contains only the numbers from a product number field, specify:


Related reference
CLEAN( ) function
EXCLUDE( ) function
INCLUDE( ) function

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