Comparison Operators¶

Comparison operators are used in conditions to compare one expression with another. The result of the comparison can be true or false or unknown.

The comparison operators are the following:

• a < b: true if a is less than b.

• a <= b: true if a is less than or equal to b.

• a > b: true if a is greater than b.

• a >= b: true if a is greater than or equal to b.

• a = b: true if a and b are equal.

• a <> b: true if a is different than b.

• a LIKE b: true if a matches the pattern b. b is an expression of type text and can include these wildcard characters:

  • % (percentage): represents a segment of text of any length, including an empty text.

  • _ (underscore): represents any character (only one character).

  Optionally, you can indicate the ESCAPE clause followed by any character. This character is called “escape character”. If the escape character is placed in front of a wildcard character (_ or %), it indicates that the wildcard has to be interpreted as a regular character and not as a wildcard.

  The default value for the escape character is the $ (dollar).

  If the pattern includes the characters % or _ and you want to consider them as literals and not wildcards, escape them by prefixing them with the escape character. That is, the character indicated in the ESCAPE parameter or if you do not indicate that, with dollar. For example, “$$”.

  Example 1) the pattern '%commerce_' matches any string ending with the substring 'commerce' followed by any character. For example, these values match this pattern: “commerce1”, “New commerce2”.

  Example 2) the following query returns the rows of the view internet_inc whose summary contain the text adsl: SELECT * FROM internet_inc WHERE summary like '%adsl%'

  Example 3) to obtain all the rows whose value for the column discount is “30%” use this condition: SELECT ... FROM ... WHERE discount LIKE '30~%' ESCAPE '~'

  The escape character ~ indicates that the percentage character after “30” does not have to be treated as a wildcard.

• a regexp_like b: true if a matches the pattern b. b is a Java regular expression.

  If you want to do a case-insensitive comparison, use the operator regexp_ilike because the performance will be better than if you use a regular expression that ignores the case (i.e. a regular expression that starts with ?i).

  Examples: Consider the following view PRODUCTS:

  IDENTIFIER	NAME
  AJ00	Product A
  AJ17	Product B
  AJ1A8	Product C
  PQ983	Product D
  PQ00	Product E

  The query SELECT * FROM products WHERE identifier regexp_like 'AJ\d+' returns the rows:

  IDENTIFIER	NAME
  AJ00	Product A
  AJ17	Product B

• a regexp_ilike b: true if a matches the pattern b, ignoring case differences. b is a Java regular expression .

• a is not NULL: true if a is not null.

• a is NULL: true if a is null.

• a is TRUE: true if a is a boolean expression and is true.

• a is FALSE: true if a is a boolean expression and is false.

• a in (b [, c]* ): true if a is equal to one or more expressions on the right-side (b, c…).

  Example: The following statement selects the tuples from the view internet_inc for which their value for the taxid attribute is B78596011 or B78596012:

  SELECT *
  FROM internet_inc
  WHERE taxid in ('B78596011', 'B78596012')
  

  To limit the maximum number of values allowed for IN operator, execute this command:

  SET 'com.denodo.vdb.interpreter.parser.condition.maxValuesOnInOperator' = '<enter an integer greater than 0>';
  

• a between b and c: true if a is greater than or equal to b and less than or equal to c.

  Example: The following two statements produce the same result: They select tuples from the view internet_inc for which their value for the iinc_id attribute is within the range of 2 and 4 (inclusive):

  SELECT *
  FROM internet_inc
  WHERE iinc_id between 2 AND 4
  

• ~: The evaluation of this operator returns a value between 0 and 1 that estimates the similarity between the two text-type operands using a variety of similarity algorithms. In addition to the operands to compare, the similarity operator receives the similarity algorithm to use and a minimum similarity threshold as parameters.

  Where the similarity between character strings reaches or exceeds the threshold, the condition is assessed as true. Where this is not the case, it is assessed as false.

  The left-hand (text-type) operand is one of the character strings to compare. The right-hand operand is a list of text-type elements. The first element in this list is the second character string to compare. The second specifies the minimum similarity threshold (a value of between 0 and 1) and the third (optional) specifies the similarity algorithm to be used.

  The algorithms available are the same as for the similarity function (see appendix SIMILARITY).

  Example: The following query returns tuples for which their customername field has a similarity of over 0.7 with the “General Motors Inc” string, using the Jaro Winkler editing distance algorithm between strings:

  SELECT *
  FROM internet_inc_cname
  WHERE customer_name ~ ('General Motors Inc','0.7','JaroWinkler')
  

• XMLExists: This operator executes an XQuery expression (XML Query) over an xml value. It returns true if it finds a match. This operator has three signatures:

  • XMLExists(XQueryExpression : text, value : xml)
    

    Returns true if there is a match of XQueryExpression in value.

  • XMLExists(XQueryExpression : text, ReadXQueryExpressionFromFile : boolean, value : xml)
    

    If ReadXQueryExpressionFromFile is true, XQueryExpression is a path to a file that contains the XQuery expression.

  • XMLExists(XQueryExpression : text, ReadXQueryExpressionFromFile : boolean, value : xml, ReadXMLValueFromFile)
    

    If ReadXQueryExpressionFromFile is true, XQueryExpression has to be a path to a file that contains the XQuery expression.

    If ReadXMLValueFromFile is true, ReadXMLValueFromFile has to be path to a file that contains the input XML.