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 }')
where:
- 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