%MVR
Synopsis
%MVR(expression)
Arguments
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). |
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()Opens in a new tab method of the %SYSTEM.UtilOpens in a new tab class:
WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",2)
This function can also be invoked from ObjectScript using the MVR()Opens in a new tab method call:
WRITE $SYSTEM.SQL.MVR("The quick, BROWN fox.")
You can reverse %MVR collation using the RevCollation()Opens in a new tab method of the %SYSTEM.UtilOpens in a new tab 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)