Caché SQL Reference
%MVR
[Home] [Back] 
InterSystems: The power behind what matters   
Class Reference   
Search:    

A MultiValue collation sequence function.
Synopsis
%MVR(expression)
Arguments
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).
Description
%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)
 
This function can also be invoked from ObjectScript using the MVR() method call:
  WRITE $SYSTEM.SQL.MVR("The quick, BROWN fox.")
 
You can reverse %MVR collation using the RevCollation() method of the %SYSTEM.Util class.
Examples
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)
 
See Also