Skip to main content

This is documentation for Caché & Ensemble.

For information on converting to InterSystems IRIS, see the InterSystems IRIS Adoption Guide and the InterSystems IRIS In-Place Conversion Guide, both available on the WRC Distributions page (login required).

Previous section


A MultiValue collation sequence function.




Argument Description
expression A string expression, which can be the name of a column, a string literal, or the result of another function, where the underlying data type can be represented as any character type (such as CHAR or VARCHAR2).


%MVR is a Caché SQL extension and is intended for MultiValue data compatibility.

%MVR returns expression converted to the MultiValue collation sequence. It is used when a string contains both numeric and non-numeric characters. %MVR divides the expression string into substrings, each substring containing either all numeric or all non-numeric characters. The numeric substrings are sorted in signed numeric order. The non-numeric substrings are sorted in case-sensitive ASCII collation sequence.

If expression is all numeric, the returned value is the same as numeric collation. If expression is all non-numeric, the returned value is the same as %SPACE collation.

You can perform the same collation conversion in ObjectScript using the Collation() method of the %SYSTEM.Util class:

  WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",2)
Copy code to clipboard

This function can also be invoked from ObjectScript using the MVR() method call:

  WRITE $SYSTEM.SQL.MVR("The quick, BROWN fox.")
Copy code to clipboard

You can reverse %MVR collation using the RevCollation() method of the %SYSTEM.Util class.


The following examples show how %MVR collation differs from default string collation in the handling of strings containing a numeric component. The first example orders the values in string sequence: 1027 appears before 107. The first example orders the values in %MVR sequence: 107 appears before 1027.

SELECT TOP 20 Name,Home_Street
FROM Sample.Person
ORDER BY Home_Street
Copy code to clipboard
SELECT TOP 20 Name,Home_Street
FROM Sample.Person
ORDER BY %MVR(Home_Street)
Copy code to clipboard

See Also