Validates a specified value based on specified minimum and maximum values and optionally a validating expression.

The following table explains the conditions in which Validate returns False. In all other cases, Validate returns True. Note that if Val is Null or a Boolean or Object value, Min, Max and Expr are ignored, and the return value is always True.

Since the Expr argument is a string, it must be enclosed in double quotation marks (double quotation marks in a script or filter in UNICOM Intelligence Reporter, single quotation marks in an SQL query). If the expression itself contains quotation marks, they should be duplicated within the string. For example, in a script, the function call Name.Validate(2, 80, "[^""]*") will result in the Expr argument having the value [^"]*.

If Describe is True and Validate returns False, it generates an error message describing the failed validation (formatted using the output locale) and stores it on the current thread. This means that any calling code that instantiates the Function Library objects and calls the Validate function directly can immediately inspect the thread's error information if the validation fails. Note that the next call to a function will clear the stored error information, so it must be inspected as soon as possible after the call to Validate. It is not possible to access the error message when Validate is called in SQL or mrScriptBasic.

When you use the Expr parameter to define a range expression to be used to validate a numeric or date value, the expression can contain one or more ranges, separated by commas. The syntax for a range is as follows:

Regular expressions are a concise and flexible notation that can be used for defining and validating patterns of text. The Function Library uses the Boost technology for regular expressions. For further information about the syntax, see:

For a deeper understanding of regular expressions, see Mastering Regular Expressions, 2nd Edition by Jeffrey E. F. Friedl, published by O'Reilly (see:

When a regular expression contains characters, it usually means that the text being searched must match those characters. However, regular expressions use a number of characters that have a special meaning. The following list provides a summary of the most common special characters used in regular expressions. (The special characters are shown in bold and are case sensitive; for example, \U is not the same as \u.) Note that the whole text value needs to match the expression in order to pass validation.

For more information about regular expression functions, see:

This SQL example uses the Validate function to test whether the value in a numeric variable is in a given range. The range has been specified using the Min and Max parameters.

Here are the results for the first five respondents in the result set:

You could achieve exactly the same results by specifying a range expression in the Expr parameter instead of using the Min and Max parameters:

This time instead of passing the name of the variable (Visits) as a parameter, the function call is preceded by the variable's name, and the variable parameter is omitted. This is an alternative way of calling the function.

Using the Expr parameter enables you to specify quite complex requirements. When you are filtering data, using the Validate function and a range expression can be easier than defining the filter using a complex expression. For example, in the following example, Validate has been used in the WHERE clause to select respondents for whom the Visits variable has a value from 5 to 10, with the exception of 7, or a value of 50 or more.

IS NOT NULL has been used to exclude respondents for whom the Visits variable stores Null. If we remove this from the WHERE clause, all the respondents for whom the Visits variable stores Null will be included, because Validate always returns True when the value is Null.

You can use the Validate function to check whether a text response meets certain requirements. For example, you can check that it has a specified number of characters, is all uppercase, is a valid product or postal code, and so on.

The following example tests whether an input text is a valid email address, that is, a valid combination of characters: any combination of alphanumeric, hyphen, and period characters, followed by the at sign (@), followed by any combination of alphanumeric, hyphen, and period characters. (It does not check that the email address exists.)

This example tests that a product code is made up of two uppercase letters followed by a hyphen (-) followed by eight numbers.

This example is an SQL query that can be run on the Museum sample data. This query uses Validate to check that the texts stored in the Name variable are between one and 45 characters long and are composed of one or more words separated by single spaces, with no punctuation.