EXCLUDE( ) function

Returns a string containing a copy of the specified string with the excluded characters removed.

Syntax

EXCLUDE(string, characters_to_exclude)

Parameters

string

Character. The value to exclude particular characters for.

characters_to_exclude

Character. A quoted string containing a list of the characters to exclude from string.

Output

Character.

Remarks

This function compares each character in string with the character listed in the characters_to_exclude parameter, and if a match occurs the character is excluded from the output string. For example, the output for EXCLUDE(“123-45-4536”, “-”) is “123454536”. If there are no matches between string and characters_to_exclude the string parameter and the output of the function are the same. For example, the output for EXCLUDE(“ACL”, “D”) is “ACL”. If you are specifying double quotes in characters_to_exclude, you must enclose them in single quotes (e.g. ‘”’).

The EXCLUDE( ) function is case-sensitive. If you specify “ID” in characters_to_exclude these characters will not be excluded from “id94022”. If there is a chance the case may be mixed, first use the UPPER( ) function to convert all characters to uppercase.

The EXCLUDE( ) function is the opposite of the INCLUDE( ) function.

Tip

If you want to exclude letters and special characters from the output, the list of characters_to_exclude might be quite long. Therefore, we recommend that you use the INCLUDE( ) function instead. For more information, please see the INCLUDE( ) function.

Examples

a. Exclude numeric values

EXCLUDE("1550 Alberni Street", "0123456789")

returns “ Alberni Street”. The return value includes a leading space, which can be removed using the LTRIM( ) function.

b. Exclude specific characters from a field

To remove a forward slash and a number sign from the Prodno field, specify:

EXCLUDE(Prodno, "/#")

Related reference
INCLUDE( ) function


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback