Handle table and column names with spaces and special characters when working with databases

Overview

By default, the Etlworks Integrator will enclose table or column names in (databases specific) quotes if it contains a space or starts with a number.

Example

PostgreSQL

• patient > patient
• 2patient > "2patient"
• First Name > "First Name"

MySQL

• patient > patient
• 2patient > `2patient`
• First Name > `First Name`

Other cases

Sometimes, it makes sense to enforce the enclosing to automatically handle edge cases such as:

• A name that contains a reserved SQL keyword, for example, FROM, SELECT, etc.
• A name that contains a special character, for example, ., ;, etc.

When the enclosing is enforced, all names will be enclosed in (databases specific) quotes:

• public.patient -> public."patient"
• First Name -> "First Name"

Process

To enforce the enclosing for all objects when configuring the database Connection, enable the option Always enclose table/column names in double quotes.