EXCLUDE( ) function
Returns a string that excludes the specified characters.
Syntax
EXCLUDE(string, characters_to_exclude)
Parameters
| Name | Type | Description |
|---|---|---|
| string | character |
The field, expression, or literal value from which to exclude characters.
|
| characters_to_exclude | character |
The list of characters to exclude. If you specify double quotation marks in characters_to_exclude, you must enclose the list of characters in single quotation marks. For example: '"-/' |
Output
Character.
Examples
Basic examples
Returns " Alberni Street", which is the input string with all numbers excluded:
EXCLUDE("1550 Alberni Street", "0123456789")
Returns all the values in the Product_Number field with the forward slash and number sign excluded:
EXCLUDE(Product_Number, "/#")
Remarks
How it works
The EXCLUDE( ) function compares each character in string with the characters listed in characters_to_exclude. If a match occurs, the character is excluded from the output string.
For example, the output for EXCLUDE("123-45-4536", "-") is "123454536".
No matching characters
If there are no matches between string and characters_to_exclude, then string and the output of the function are the same.
For example, the output for EXCLUDE("ABC", "D") is "ABC".
Case sensitivity
The EXCLUDE( ) function is case-sensitive. If you specify "ID" in characters_to_exclude, these characters are not excluded from "id#94022". If there is a chance the case may be mixed, use the UPPER( ) function to convert string to uppercase.
For example:
EXCLUDE(UPPER("id#94022"), "ID#")
Usage tip
Use EXCLUDE( ) if the set of characters you want to exclude is small, and the set you want to include is large.
Excluding both single and double quotation marks
Quotation marks are used as string delimiters, therefore to exclude both single and double quotation marks you must nest EXCLUDE( ) so that there is a single function for each type of quotation mark:
EXCLUDE(EXCLUDE(field_to_process, "'"), '"')
Related functions
The EXCLUDE( ) function is the opposite of the INCLUDE( ) function.
