InterSystems IRIS Data Platform 2019.2  /  InterSystems SQL Reference  /  SQL Functions

InterSystems SQL Reference
Previous section           Next section
InterSystems: The power behind what matters   

A collation function that converts numbers to canonical collation format.

%PLUS expression
expression An expression, which can be the name of a column, a number or a string literal, an arithmetic expression, or the result of another function, where the underlying data type can be represented as any character type.
%PLUS converts numbers or numeric strings to canonical form, then returns these expression values in numeric collation sequence.
A number can contain leading and trailing zeros, multiple leading plus and minus signs, a single decimal point indicator (.), and the E exponent indicator. In canonical form, all arithmetic operations are performed, exponents are expanded, signs are resolved to either a single leading minus sign or no sign, and leading and trailing zeros are stripped.
A numeric literal can be specified with or without enclosing string delimiters. If a string contains non-numeric characters, %PLUS truncates the number at the first non-numeric character, and returns the numeric part in canonical form. A non-numeric string (any string that begins with a non-numeric character) is returned as 0. %PLUS also returns NULLs as 0.
%PLUS is an InterSystems SQL extension and is intended for SQL lookup queries.
You can perform the same collation conversion in ObjectScript using the Collation() method of the %SYSTEM.Util class:
  WRITE $SYSTEM.Util.Collation("++007.500",3)
Compare %PLUS to %MVR collation, which sorts a string based on the numeric substrings within the string.
The following examples uses %PLUS to return Home_Street addresses in numeric order:
SELECT Name,Home_Street
FROM Sample.Person
ORDER BY %PLUS(Home_Street)
Note that the above example orders the integer part of the street address in ascending numerical order. Compare this with the following ORDER BY example, which orders records by street addresses in collation sequence:
SELECT Name,Home_Street
FROM Sample.Person
ORDER BY Home_Street
See Also

Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-10-22 04:57:16