$JUSTIFY

docs.intersystems.com

Home / Application Development: Core Topics / InterSystems SQL Reference / SQL Functions / $JUSTIFY

InterSystems SQL Reference

$JUSTIFY

InterSystems: The power behind what matters

A function that right-aligns a value within a specified width, optionally rounding to a specified number of fractional digits.

Synopsis

$JUSTIFY(expression,width[,decimal])

Arguments

expression	The value that is to be right-aligned. It can be a numeric value, a string literal, or an expression that resolves to a numeric or string.
width	The number of characters within which expression is to be right-aligned. A positive integer or an expression that evaluates to a positive integer.
decimal	Optional — The number of fractional digits. A positive integer or an expression that evaluates to a positive integer. InterSystems IRIS rounds or pads the number of fractional digits in expression to this value. If you specify decimal, InterSystems IRIS treats expression as a numeric.

Description

$JUSTIFY returns the value specified by expression right-aligned within the specified width. You can include the decimal argument to decimal-align numbers within width.

$JUSTIFY(expression,width): the 2-argument syntax right-justifies expression within width. It does not perform any conversion of expression. The expression can be a numeric or a nonnumeric string.
$JUSTIFY(expression,width,decimal): the 3-argument syntax converts expression to a canonical number, rounds or zero pads fractional digits to decimal, then right-justifies the resulting numeric value within width. If expression is a nonnumeric string or NULL, InterSystems IRIS converts it to 0, pads it, then right-justifies it.

$JUSTIFY recognizes the DecimalSeparator character for the current locale. It adds or deletes a DecimalSeparator character as needed. The DecimalSeparator character depends upon the locale; commonly it is either a period (.) for American-format locales, or a comma (,) for European-format locales. To determine the DecimalSeparator character for your locale, invoke the following method:

  WRITE ##class(%SYS.NLS.Format).GetFormatItem("DecimalSeparator")

Commonly, $JUSTIFY is used to format numbers with fractional digits: every number is given the same number of fractional digits, and the numbers are right-aligned so that the DecimalSeparator characters align in a column of numbers.

$JUSTIFY and LPAD

The two-argument form of LPAD and the two-argument form of $JUSTIFY both right-align a string by padding it with leading spaces. These two-argument forms differ in how they handle an output width that is shorter than the length of the input expression: LPAD truncates the input string to fit the specified output length. $JUSTIFY expands the output length to fit the input string. This is shown in the following example:

SELECT '>'||LPAD(12345,10)||'<' AS lpadplus,
       '>'||$JUSTIFY(12345,10)||'<' AS justifyplus,
       '>'||LPAD(12345,3)||'<' AS lpadminus,
       '>'||$JUSTIFY(12345,3)||'<' AS justifyminus

The three-argument form of LPAD allows you to left pad with characters other than spaces.

Arguments

expression

The value to be right-justified, and optionally expressed as a numeric with a specified number of fractional digits.

If string justification is desired, do not specify decimal. The expression can contain any characters. $JUSTIFY right-justifies expression, as described in width.
If numeric justification is desired, specify decimal. If decimal is specified, $JUSTIFY converts expression to a canonical number. It resolves leading plus and minus signs and removes leading and trailing zeros. It truncates expression at the first nonnumeric character. If expression begins with a nonnumeric character (such as a currency symbol), $JUSTIFY converts the expression value to 0. For further details on how InterSystems IRIS converts a numeric to a canonical number, and InterSystems IRIS handling of a numeric string containing nonnumeric characters, refer to the Numbers section of the “Data Types and Values” chapter of Using ObjectScript.

After $JUSTIFY converts expression to a canonical number, it zero-pads or rounds this canonical number to decimal number of fractional digits, then right-justifies the result, as described in width. $JUSTIFY does not recognize NumericGroupSeparator characters, currency symbols, multiple DecimalSeparator characters, or trailing plus or minus signs.

width

The width in which to right-justify the converted expression. If width is greater than the length of expression (after numeric and fractional digit conversion), InterSystems IRIS right-justifies to width, left-padding as needed with blank spaces. If width is less than the length of expression (after numeric and fractional digit conversion), InterSystems IRIS sets width to the length of the expression value.

Specify width as a positive integer. A width value of 0, the empty string (''), NULL, or a nonnumeric string is treated as a width of 0, which means that InterSystems IRIS sets width to the length of the expression value.

decimal

The number of fractional digits. If expression contains more fractional digits, $JUSTIFY rounds the fractional portion to this number of fractional digits. If expression contains fewer fractional digits, $JUSTIFY pads the fractional portion with zeros to this number of fractional digits, adding a Decimal Separator character, if needed. If decimal=0, $JUSTIFY rounds expression to an integer value and deletes the Decimal Separator character.

If the expression value is less than 1, $JUSTIFY inserts a leading zero before the DecimalSeparator character.

The $DOUBLE values INF, -INF, and NAN are returned unchanged by $JUSTIFY, regardless of the decimal value.

Examples

The following Dynamic SQL example performs right-justification on strings. No numeric conversion is performed:

  ZNSPACE "SAMPLES"
  SET myquery = "SELECT TOP 20 Age,$JUSTIFY(Name,18),DOB FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"

The following Dynamic SQL example performs numeric right-justification with a specified number of fractional digits:

  ZNSPACE "SAMPLES"
  SET myquery = 2
  SET myquery(1) = "SELECT TOP 20 $JUSTIFY(Salary,10,2) AS FullSalary,"
  SET myquery(2) = "$JUSTIFY(Salary/7,10,2) AS SeventhSalary FROM Sample.Employee"
   SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"

The following Dynamic SQL example performs numeric right-justification with a specified number of fractional digits, and string right-justification of the same numeric value:

  SET myquery = 2
  SET myquery(1) = "SELECT $JUSTIFY({fn ACOS(-1)},8,3) AS ArcCos3,"
  SET myquery(2) = "$JUSTIFY({fn ACOS(-1)},8) AS ArcCosAll"
   SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()

The following Dynamic SQL example performs numeric right-justification with the $DOUBLE values INF and NAN:

  DO ##class(%SYSTEM.Process).IEEEError(0)
  SET x=$DOUBLE(1.2e500)
  SET y=x-x
  SET myquery = 2
  SET myquery(1) = "SELECT $JUSTIFY(?,12,2) AS INFtest,"
  SET myquery(2) = "$JUSTIFY(?,12,2) AS NANtest"
   SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute(x,y)
  DO rset.%Display()