Rename Column

If your transformation type is Rename Column, you can do the following:

• Rename column to (string)
• Add a prefix or suffix
• Remove a prefix or suffix
• Replace a prefix or suffix with different characters
• Convert column name to uppercase
• Convert column name to lowercase
• Rename column (expression)

Rename column to (string)

Use the Rename column to: [string] option to change the name of all columns that you defined in the Transformation scope step to a different name. For example, if you have a table called SALARY and want to change all instances of this name to EMP then enter the string SAL. You can enter any string in this field.

Add a prefix or suffix

Use the Add a prefix or suffix option to add additional characters to the beginning or end of the column name for all columns that fit the definition you created in the Transformation scope step. For example, if the column name is SALARY, you can add a suffix, such as TAR or _TAR to the table name for all tables with that table name. In this case, the resulting table name will be SALARYTAR or SALARY_TAR.

To globally add a prefix or suffix:

1. Select Add <Prefix/Suffix> Insert Characters to matching column names.
2. Click the word Prefix or Suffix and select one of these two from the list.
3. Click the [string] to activate the field.
4. Type the characters you want as the prefix or suffix. If you want to include an underscore or other legal character to separate the prefix/suffix from the original name, you must add it as part of the character string.
5. Click Finish to add the rule to the Global Rules list.

Remove a prefix or suffix

Use the Remove a prefix or suffix option to remove a string of characters from the beginning or end of a column name for all columns that fit the definition you created in the Transformation scope step.

For example, you can use this option to remove the letters _REV from the column name for all columns with the name SALARY. In this case the column name in the target will be SALARY.

To globally remove a prefix or suffix:

1. Select Remove <Prefix/Suffix> Insert Characters from matching column names.
2. Click the word Prefix or Suffix and select one of these two from the list.
3. Click [string] to activate the field.
4. Type the characters you want to remove. If you want to remove an underscore or other legal character from the original name, you must add it as part of the character string.
5. Click Finish to add the rule to the Global Rules list.

Replace a prefix or suffix with different characters

Use the Replace a prefix or suffix option to replace a string of characters with a different string of characters. You determine whether to replace the characters at the beginning or end of a column name for all columns that fit the definition you created in the Transformation scope step.

For example, you can use this option to replace the letters _ORIG with _REPL in the column names for all columns called SALARY_ORIG. In this case the column name in the target will be SALARY_REPL.

To globally replace a prefix or suffix:

1. Select Replace <Prefix/Suffix> Insert Characters by Insert Characters for all matching schema names.
2. Click the word Prefix or Suffix and select one of these two from the list.
3. Click the first [string] to activate the field.
4. Type the characters from the existing (source) column that you want to replace. If you want to include an underscore or other legal character from the original name in the string that you want to replace, you must add it as part of the character string.
5. Click the second [string] to activate the field.
6. Type the characters you want to use in the target. These characters replace the original (source) characters in the target.
7. Click Finish to add the rule to the Global Rules list.

Convert column name to uppercase

Use the convert to uppercase option to convert a column name to all upper case. For example:

• Column_cat, becomes COLUMN_CAT
• column_cat, becomes COLUMN_CAT
• coLUMnM_Cat, becomes COLUMN_CAT

To globally change the table name to all uppercase

1. Select Convert column name to uppercase.
2. Click Finish to add the rule to the Global Rules list.

Convert column name to lowercase

Use the convert to lowercase option to convert a column name to all lower case. For example:

• Column_cat, becomes column_cat
• column_cat, becomes column_cat
• coLUMnM_Cat, becomes column_cat

To globally change the column name to all lowercase:

1. Select Convert column name to lowercase.
2. Click Finish to add the rule to the Global Rules list.

Rename column (expression)

Use the Rename column to [expression] option to change the name of all tables that fit the definition you created in the Transformation scope step. For instance, if you have a column called SALARY and want to change it to SAL.

To change the column name:

1. Select Rename column to: [expression]

2. Click the button to the right of the Rename column option to open the Expression Editor. For information on how to use the Expression Editor, see Using the Expression Builder. Then go to step 4.

   or

   Click [expression] to activate the field and continue with step 3.

3. Type an SQLite expression or a string (in quotes) to rename the column. For example:

   • "New_Column"
   • ’PREF_’||$COLUMN_NAME_VAR||’_SUFF’

   You can use the following variables in the SQLite expression:

   • $SCHEMA_NAME_VAR
   • $TABLE_NAME_VAR
   • $COLUMN_NAME_VAR
   • $COLUMN_DATATYPE_VAR