REMOVE( ) function
Returns a string that includes only the specified characters.
Syntax
REMOVE(string, valid_characters)
Parameters
Name | Type | Description |
---|---|---|
string | character | The string 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
Note
The REMOVE( ) function has been superseded by the INCLUDE( ) and EXCLUDE( ) functions.
REMOVE( ) is still available in the current version of Analytics for backwards compatibility with earlier versions.
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.