Skip to main content

BETWEEN

Matches a value to a range of values.

Synopsis

scalar-expression BETWEEN lowval AND highval

Arguments

Argument Description
scalar-expression A scalar expression (most commonly a data column) whose values are being compared with the range of values between lowval and highval (inclusive).
lowval Expression that resolves to the low collation sequence value specifying the beginning of a range of values to match with each value in scalar-expression.
highval Expression that resolves to the high collation sequence value specifying the end of a range of values to match with each value in scalar-expression.

Description

The BETWEEN predicate allows you to select those data values that are in the range specified by lowval and highval. This range is inclusive of the lowval and highval values themselves. This is equivalent to a paired greater than or equal to operator and a less than or equal to operator. This comparison is shown in the following example:

SELECT Name,Age FROM Sample.Person
WHERE Age BETWEEN 18 AND 21
ORDER BY Age

This returns all the records in the Sample.Person table with an Age value between 18 and 21, inclusive of those values. Note that you must specify the BETWEEN values in ascending order; a predicate such as BETWEEN 21 AND 18 would return the null string. If none of the scalar expression values fall within the specified range, BETWEEN returns the null string.

Like most predicates, BETWEEN can be inverted using the NOT logical operator. Neither BETWEEN nor NOT BETWEEN can be used to return NULL fields. To return NULL fields use IS NULL. NOT BETWEEN is shown in the following example:

SELECT Name,Age FROM Sample.Person
WHERE Age NOT BETWEEN 20 AND 55
ORDER BY Age

This returns all the records in the Sample.Person table with an Age value less than 20 or greater than 55, exclusive of those values.

BETWEEN can be used wherever a predicate condition can be specified, as described in the Overview of Predicates page of this manual.

Collation Types

BETWEEN is commonly used for a range of numeric values, which collate in numeric order. However, BETWEEN can be used for a collation sequence range of values of any data type.

BETWEEN uses the same collation type as the column it is matching against. By default, string data types collate as SQLUPPER, which is not case-sensitive. The “Collation” chapter of Using Caché SQL provides details on defining the string collation default for the current namespace and specifying a non-default field collation type when defining a field/property.

If your query assigns a different collation type to the column, you must also apply this collation type to the BETWEEN substring. This is shown in the following examples:

In the following example, BETWEEN uses the fields’ default letter case collation, SQLUPPER, which is not case-sensitive. It returns records where Name is higher in alphabetical order than Home_State, and Home_State is higher in alphabetical order than Home_City:

SELECT Name,Home_State,Home_City
FROM Sample.Person
WHERE Home_State BETWEEN Name AND Home_City
ORDER BY Home_State

In the following example, BETWEEN string comparisons are not case-sensitive, because the Home_State field is defined as SQLUPPER. This means that the lowval and highval are functionally identical, selecting 'MA' in any lettercase:

SELECT Name,Home_State FROM Sample.Person
WHERE Home_State
   BETWEEN 'MA' AND 'Ma'
ORDER BY Home_State

In the following example, the %SQLSTRING collation function causes BETWEEN string comparisons to be case-sensitive. It selects those records with Home_State values of 'MA' through 'Ma', which in this data set includes 'MA', 'MD', 'ME', 'MO', 'MS', and 'MT':

SELECT Name,Home_State FROM Sample.Person
WHERE %SQLSTRING(Home_State) 
   BETWEEN %SQLSTRING('MA') AND %SQLSTRING('Ma')
ORDER BY Home_State

In the following example, the BETWEEN string comparison is not case-sensitive and ignores blank spaces and punctuation marks:

SELECT Name FROM Sample.Person
WHERE %STRING(Name) BETWEEN %STRING('OA') AND %STRING('OZ')
ORDER BY Name

Using %STRING, this example can select Odem, O'Donnell, and Olsen. Without the %STRING collation type, O'Donnell would not be selected.

Refer to %SQLUPPER for further information on case transformation functions.

The following example shows BETWEEN used in an INNER JOIN operation ON clause. It is performing a string comparison which is not case-sensitive:

SELECT P.Name AS PersonName,E.Name AS EmpName 
FROM Sample.Person AS P INNER JOIN Sample.Employee AS E
ON P.Name BETWEEN 'an' AND 'ch' AND P.Name=E.Name

%SelectMode

If %SelectMode is set to a value other than Logical format, the BETWEEN predicate values must be specified in the %SelectMode format (ODBC or Display). This applies mainly to dates, times, and Caché format lists (%List). Specifying predicate value(s) in Logical format commonly results in an SQLCODE error. For example, SQLCODE -146 “Unable to convert date input to a valid logical date value”.

In the following Dynamic SQL example, the BETWEEN predicate must specify dates in %SelectMode=1 (ODBC) format:

  ZNSPACE "SAMPLES"
  SET q1 = "SELECT Name,DOB FROM Sample.Person "
  SET q2 = "WHERE DOB BETWEEN '1950-01-01' AND '1960-01-01'"
  SET myquery = q1_q2
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=1
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"

See Also

FeedbackOpens in a new tab