Skip to main content
Previous section

%MVR

A MultiValue collation sequence function.

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() 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.

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
Copy code to clipboard
SELECT TOP 20 Name,Home_Street
FROM Sample.Person
ORDER BY %MVR(Home_Street)
Copy code to clipboard

See Also