1. Reference for Oracle GoldenGate
  2. Oracle GoldenGate Common Components
  3. Column Conversion Functions
  4. @STRNUM

@STRNUM

Use the @STRNUM function to convert a number into a string and specify the output format and padding.

Syntax

@STRNUM (column, {LEFT | LEFTSPACE, | RIGHT | RIGHTZERO} [length] )
column

The name of a source numeric column.

LEFT

Left justify, without padding.

LEFTSPACE

Left justify, fill the rest of the target column with spaces.

RIGHT

Right justify, fill the rest of the target column with spaces. If the value of a column is a negative value, the spaces are added before the minus sign. For example, strnum(Col1, right) used for a column value of -1.27 becomes ###-1.27, assuming the target column allows 7 digits. The minus sign is not counted as a digit, but the decimal is.

RIGHTZERO

Right justify, fill the rest of the target column with zeros. If the value of a column is a negative value, the zeros are added after the minus sign and before the numbers. For example, strnum(Col1, rightzero) used for a column value of -1.27 becomes -0001.27, assuming the target column allows 7 digits. The minus sign is not counted as a digit, but the decimal is.

length

Specifies the output length, when any of the options are used that specify padding (all but LEFT). For example:

  • strnum(Col1, right, 6) used for a column value of -1.27 becomes ##-1.27. The minus sign is not counted as a digit, but the decimal is.

  • strnum(Col1, rightzero, 6) used for a column value of -1.27 becomes -001.27. The minus sign is not counted as a digit, but the decimal is.

Example

Assuming a source column named NUM has a value of 15 and the target column's maximum length is 5 characters, the following examples show the different types of results obtained with formatting options.

Function statement Result (# denotes a space) 
CHAR1 = @STRNUM (NUM, LEFT)
 
15
 
CHAR1 = @STRNUM (NUM, LEFTSPACE)
 
15###
 
CHAR1 = @STRNUM (NUM, RIGHTZERO)
 
00015
 
CHAR1 = @STRNUM (NUM, RIGHT)
 
###15

If an output length of 4 is specified in the preceding example, the following shows the different types of results.

Function statement Result (# denotes a space) 
CHAR1 = @STRNUM (NUM, LEFTSPACE, 4)
 
15##
 
CHAR1 = @STRNUM (NUM, RIGHTZERO, 4)
 
0015
 
CHAR1 = @STRNUM (NUM, RIGHT, 4)
 
##15