Range searches

With a range search, you match terms that are between the lower and upper bounds specified by the query. Range searches can be inclusive or exclusive of the upper and lower bounds. Sorting is in lexicographical order (also known as dictionary order or alphabetic order).

Lexicographical order does not give the expected results to numeric data unless all numbers have the same number of digits. If necessary, add zeros to the beginning of numbers to provide the necessary number of digits.

Range searches use the keyword TO to separate search terms. By default, the word "to" is a stopword and is not an indexed term. If you are using a stopword list that does not include the word "to" or you are not using a stopword list, omit the word TO from the range query.

Inclusive range searches

Use brackets ([ ]) in the search predicate to specify an inclusive search. The syntax is [searchterm1 TO searchterm2].

The following search predicate finds all terms between apple and orange, including the terms apple and orange:

bts_contains(column, ' [apple TO orange] ')

This example finds all terms between 20063105 and 20072401, including 20063105 and 20072401:

bts_contains(column, ' [20063105 TO 20072401] ')

Exclusive range searches

Use braces ({ }) in the search predicate to specify an exclusive search. The syntax is {searchterm1 TO searchterm2}.

The following search predicate finds all terms between Beethoven and Mozart, excluding the terms Beethoven and Mozart:

bts_contains(column, ' {Beethoven TO Mozart} ')

This example finds all terms between 65 and 89, excluding 65 and 89:

bts_contains(column, ' {65 TO 89} ')