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