TRANS FILL Function

Use the TRANS FILL function to replace the data in a CHAR, VARCHAR or CLOB column with a character that you specify. You can replace some or all of the characters in the source column.

The syntax for TRANS FILL is:

TRANS FILL ('repchar=c, length= {n | match | max }')

repchar=c
Specify the replacement character. Allowable value is any alphabetic character from a through z.
length={n | match | max}
Specify the replacement character length using one of these values:
n
Number of times to repeat the replacement character at the destination. If you specify a value for n that is less than the length of the source column, the remainder of the column is filled with nulls.The value you select for n cannot exceed the defined length of the destination column.
match
Replace the source characters with an equal number of replacement characters.
max
Replace the entire length of the source column with replacement characters.
Note:
  • When TRANS FILL replaces CLOB column data, the replacement characters will be in ASCII format.
  • For a CLOB column, if you use length=max, Optim calculates the supported CLOB size for the database and fills the column accordingly.
  • If a source CLOB column is null and the destination column is null-eligible, null will be used as the replacement.
  • If a source CLOB column is null and the destination column does not allow nulls, the row is skipped.
Examples:
  • TRANS FILL ('repchar=z,length=4')
    Replaces source data with zzzz
  • TRANS FILL ('repchar=n,length=max')
    Replaces source data with n to fill the length of the column
  • TRANS FILL ('repchar=c,length=match')
    Replaces a source column that contains Optim with ccccc