REVERSE - InterSystems SQL Reference - InterSystems IRIS Data Platform 2018.1

docs.intersystems.com

Home / InterSystems SQL Reference / SQL Functions / REVERSE

InterSystems SQL Reference

REVERSE

InterSystems: The power behind what matters

A scalar string function that returns a character string in reverse character order.

Synopsis

REVERSE(string-expression)

Arguments

string-expression

The string expression to be reversed. The expression can be the name of a column, a string literal, a numeric, or the result of another scalar function, where the underlying data type can be represented as any character type (such as CHAR or VARCHAR).

Description

REVERSE returns string-expression with its character order reversed. For example, 'Hello World!' is returned as '!dlroW olleH'. This is a simple string-order reversal, with no additional processing.

The string returned is data type VARCHAR, regardless of the data type of the input value. Numbers are converted to canonical form, numeric strings are not converted to canonical form before reversing.

Leading and trailing blanks are unaffected by reversing.

Reversing a NULL value results in a NULL.

Note:

Because REVERSE always returns a VARCHAR string, some types of data become invalid when reversed:

A reversed list is no longer a valid list and cannot be converted from storage format to display format.
A reversed date is no longer a valid date, and cannot be converted from storage format to display format.

Examples

The following example reverses the Name field values. In this case, this results in names sorted by middle initial:

SELECT Name,REVERSE(Name) AS RevName
FROM Sample.Person
ORDER BY RevName

Note that because Name and RevName are just different representations of the same field, ORDER BY RevName and ORDER BY RevName,Name perform the same ordering.

The following example reverses a number and a numeric string:

SELECT REVERSE(+007.10) AS RevNum,
       REVERSE('+007.10') AS RevNumStr

The following Embedded SQL example reverses a $DOUBLE number:

  SET dnum=$DOUBLE(1.1)
  &sql(SELECT REVERSE(:dnum) INTO :drevnum)
  WRITE dnum,!
  WRITE drevnum,!

The following example shows what happens when you reverse a list:

SELECT FavoriteColors,REVERSE(FavoriteColors) AS RevColors
FROM Sample.Person

The following example shows what happens when you reverse a date:

SELECT DOB,%INTERNAL(DOB) AS IntDOB,REVERSE(DOB) AS RevDOB
FROM Sample.Person