|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.
WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",2)
This function can also be invoked from ObjectScript using the MVR() method call:
WRITE $SYSTEM.SQL.MVR("The quick, BROWN fox.")
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
SELECT TOP 20 Name,Home_Street FROM Sample.Person ORDER BY %MVR(Home_Street)