Scripting > UNICOM Intelligence Function Library > Miscellaneous functions > Validate
 
Validate
Validates a specified value based on specified minimum and maximum values and optionally a validating expression.
Syntax
Validate(Val [, Min [, Max [, Expr [, Describe ] ] ] ])
Parameters
Val
The value to test.
Type: None
Min
Optional. The minimum value allowed. The default is -1.79769313486232e308.
Type: Double
Max
Optional. The maximum value allowed. The default is 1.79769313486232e308.
Type: Double
Expr
Type: Text
Optional. Expression for a text, numeric, or date value. For a numeric or date value, the expression can specify one or more valid ranges. For a text value, the expression defines a regular expression that can be used to validate the text.
Describe
Type: Boolean
Optional. Whether to generate a description for a failed test. The default is False
(return)
Type: Boolean
True if Val fits the conditions, False otherwise.
Remarks
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.
When Val is
Validate returns False when
Text
Min is supplied and Val has fewer than Min characters.
 
Max is supplied and Val has more than Max characters.
 
Expr is supplied and Val doesn’t match the regular expression (see below). Note that the whole text value needs to match the expression in order to pass validation.
Numeric (Long or Double)
Min is supplied and Val < Min.
 
Max is supplied and Val > Max.
 
Expr is supplied and Val isn’t in any of the defined valid ranges (see below).
Categorical
Min is supplied and Val has fewer than Min responses.
 
Max is supplied and Val has more than Max responses.
Date
Min is supplied and Val is earlier than the date whose internal representation is Min.
 
Max is supplied and Val is later than the date whose internal representation is Max.
 
Expr is supplied and Val isn't in any of the defined valid ranges (see below).
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.
Range expression syntax
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:
An optional negation operator, which is the caret symbol (^).
An optional lower bound value.
An optional upper bound value.
If both values are supplied, they must be separated by the range operator, which is two periods with no space in between (..).
The range operator can appear when either or both values are missing.
A range lower or upper bound value is a sequence of characters that can be interpreted as a floating point number (if Val is Long or Double), or as a date or a floating point number (if Val is a Date), according to the given input locale.
Any value can optionally be enclosed in single or double quotation marks. This is necessary if the value contains either a comma or a pair of periods, in order that they are not interpreted as separators between ranges or between range values.
Examples of range expressions
5 .. 25
Val must be >= 5 and <= 25.
^ 5 .. 25
Val must not be >= 5 and <= 25 (in other words, it must be < 5 or > 25).
.. 10, 20, 30 .., ^70
Val must be <= 10, or 20, or >= 30, but not 70.
'10,5' .. "11,5", ^ '11,0'
With a Danish (or similar) input locale, Val must be >= 10.5 and <= 11.5, but not 11.0.
'Jan 1, 1960' .. 1980-12-31 23:59:59
Val must be (a date) later than or equal to 00:00:00 on January 1st 1960 and before or equal to 23:59:59 on December 31st 1980.
..
Any value is allowed.
^                                     
No value is allowed.
Regular expression syntax
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:
http://www.boost.org/doc/libs/1_57_0/libs/regex/doc/html/index.html
For a deeper understanding of regular expressions, see Mastering Regular Expressions, 2nd Edition by Jeffrey E. F. Friedl, published by O'Reilly (see:
http://www.oreilly.com/catalog/regex2/
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.
Special character
Description
.
Any character (including newline)
[abcn-z]
Any of the characters a, b, c, n, o, p, ..., z
\w
Any alphanumeric character (including accents) or underscore (_)
\l
Any lower-case character (including accents)
\u
Any upper-case character (including accents)
\d
Any numeric character
\s
A whitespace character
xxx|yyy
Either xxx or yyy
(xxx)
Grouping (subexpression)
x*
Zero or more occurrences of x
x+
One or more occurrences of x
x?
Zero or one occurrences of x
(xxx){ m }
Exactly m occurrences of xxx
(xxx){ m , n }
At least m and at most n occurrences of xxx
\
The escape character that you use to match characters that have a special meaning in regular expressions, such as the following characters , . ? { } [ ] ( ) $ ^ *. For example, to match the { character, specify \{
For more information about regular expression functions, see:
RegExFind
RegExReplace
RegExTest
Examples
Validating a numeric variable
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.
SELECT Respondent.Serial,
Visits,
Validate(Visits, 5, 10) AS Valid
FROM vdata
WHERE Visits IS NOT NULL
Here are the results for the first five respondents in the result set:
Respondent.Serial Visits Valid
----------------- ------ ------
1 1 False
4 6 True
5 2 False
6 1 False
7 1 False
You could achieve exactly the same results by specifying a range expression in the Expr parameter instead of using the Min and Max parameters:
SELECT Respondent.Serial,
Visits,
Visits.Validate(, , '5..10') AS Valid
FROM vdata
WHERE Visits IS NOT NULL
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.
SELECT Respondent.Serial, Visits
FROM vdata
WHERE Visits IS NOT NULL
AND Validate(Visits, , , '5..10, ^7, 50..')
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.
Validating a text variable
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.)
Dim Valid
Valid = Validate("John-Smith@test.com", , , "[-.\w]+\@[-.\w]+")
This example tests that a product code is made up of two uppercase letters followed by a hyphen (-) followed by eight numbers.
Dim Valid
Valid = Validate("ID-86442000", , , "\u{2}-\d{8}")
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.
SELECT Respondent.Serial,
Name,
Name.Validate(1, 45, '\w+(\s\w+)+')
FROM vdata
See also
Execute
Miscellaneous functions