KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: The New "Contains keyword" Operator
PRODUCT: 4D | VERSION: 11.1 | PLATFORM: Mac & Win
Published On: January 28, 2009

In prior versions of 4D the String Comparison Operators consisted of the operator set of:

  • =
  • #
  • >
  • <
  • >=
  • <=

    An expression that uses a comparison operator returns a Boolean value, either TRUE or FALSE. 4D v11 SQL adds the new String comparison operator "%" called the "Contains keyword" operator.

    The "%" operator works only on whole words. Strings are compared on a character-by-character basis except in the case of searching by keywords. Comparisons using the keyword operator are based on "Word Boundaries" as defined in "Unicode Standard Annex #29", "Unicode Text Segmentation". For more information about the rules concerning how keywords are taken into account, please refer to the following address:

    https://www.unicode.org/unicode/reports/tr29/#Word_Boundaries.

    Prior to the introduction of the "keywords" operator, to find a "word" contained in a block of text was accomplished by using the "contains" construction of "@word@". This type of comparison uses the character-by-character comparison technique. Since this technique does not account for word separators the result could produce a True result if a word were contained within a larger word.

    Unlike other string comparisons, searching by keywords looks for "words" in "texts": words are considered both individually and as a whole. The % operator always returns False if the query concerns several words or only part of a word (for example, a syllable). The "words" are character strings surrounded by "separators", which are spaces and punctuation, characters and dashes. An apostrophe, like in "Today's", is considered as part of the word. Numbers can be searched for because they are evaluated as a whole (including decimal symbols). Other symbols (currency, temperature, and so on) will be ignored.

    "Alpha Bravo Charlie" % "Bravo" ` Returns True
    "Alpha Bravo Charlie" % "vo" ` Returns False
    "Alpha Bravo Charlie" % "Alpha Bravo" ` Returns False
    "Alpha,Bravo,Charlie" % "Alpha" ` Returns True
    "Software and Computers" % "comput@" ` Returns True

    • Commented by Luis Pineiros on April 3, 2009 at 10:58 AM
    For more examples and information about Keyword support in 4D v11 SQL please refer to: http://kb.4d.com/search/assetid=75200