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. 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 Survey Tabulation, 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. 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:

The following table provides some examples.

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:

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 table 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.) The whole text value needs to match the expression in order to pass validation.

The following SQL example provides a simple example of using the Validate function to test whether the value in a numeric variable is within a given range. In this example, 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 get 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 with the variable's name and omitted the variable parameter. 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 you 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, uses Validate to test whether an input text is a valid email address. (It doesn't check that the email address actually exists, it just checks that the text is a valid combination of characters: in this case, any combination of alphanumeric, hyphen, and period characters followed by the at sign (@), followed by any combination of alphanumeric, hyphen, and period characters.)

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

The next 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.