REMOVE( ) function

Returns a string that includes only the specified characters.

Syntax

REMOVE(string, valid_characters)

Parameters

Name	Type	Description
string	character	The field, expression, or literal value to remove characters from.
valid_characters	character	The characters to retain in string. If you specify double quotation marks in valid_characters, you must enclose the list of characters in single quotation marks. For example: '"-/' Note If a character you specify does not appear in string, it is not included in the return value.

Output

Character.

Examples

Basic examples

Returns "ABC123     ":

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

Returns "ABC123XX    ":

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

Returns "1234       ":

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

Returns all the values in the Product_Number field with all non-numeric characters removed:

REMOVE(Product_Number,"0123456789")

Remarks

How it works

The REMOVE( ) function removes unwanted characters from character data and returns a fixed length string.

When to use REMOVE( )

Use REMOVE( ) to normalize data fields that do not have a consistent format, such as address fields. You can also use REMOVE( ) to remove punctuation or other invalid information from poorly edited fields.

You can also use the function to clean data in fields before using the SORT or JOIN commands, for duplicate matching, or for report output.

Case sensitivity

The REMOVE( ) function is case-sensitive. If you specify "ID" in valid_characters, these characters are not included in "id#94022". If there is a chance the case may be mixed, use the UPPER( ) function to convert string to uppercase.

For example:

REMOVE(UPPER("id#94022"), "ID0123456789")

Related functions

REMOVE( ) is similar to the INCLUDE( ) function, with the following difference:

• REMOVE( ) adds blanks to the end of the output to replace the characters that have been removed. The original length of string is retained.
• INCLUDE( ) does not add any blanks.