NEAR() performs a fuzzy comparison and returns true if the specified difference between two values is equal to or less than the specified difference, otherwise NEAR() returns false. NEAR() can be used on pairs of numeric, date or character fields or values.
Unlike SIMILAR(), NEAR() doesnt convert the data, and the difference specified relates specifically to the type of data being compared.
Because of this, NEAR() is ideal for measuring true nearness of strings, and especially actual nearness of numeric values and dates. Additionally, NEAR() provides the user with more control over assessing true nearness because the difference specified will be dependent on the type of fields or values being compared. The fields or values being compared be must of the same type (numeric, character or date).
| | for numeric fields or values, the difference is specified as either zero or a positive number |
| | for datetime fields or values, the difference is specified as either zero or a positive number of days. For measuring differences in hours minutes or seconds, difference must be a expressed as a fraction of a day, most typically using an elapsed time literal |
| | for character fields or values, the difference is specified as either zero or a positive whole number representing the Damerau-Levenshtein distance. |
Note: The fields or values being compared cannot be logical.
When performing difference comparisons on strings, the NEAR() function is significantly more efficient than the companion DIFFERENCE() function.
For more information on the Damerau-Levenshtein distance, see the function DIFFERENCE().
For more information on the SIMILAR() function, see SIMILAR().
To better understand using Near and Similar options, see NEAR() versus SIMILAR() Functions.
NEAR(value1,value2,difference)
NEAR() returns true if the difference between value1 and value2 is equal to or less than the specified difference, otherwise NEAR() returns false.
Note: Either value1 or value2 can be expressed as a constant.
NEAR(1093,1079,50) = T
NEAR(JANE,JAMES,2) = T
NEAR(JANE,JAMES,1) = F
NEAR(20110130,20110215,10) = F
NEAR(10:25:42,11:19:30,e01)=T
NEAR(10:25:42,11:50:30,e01)=F