Skip to content

Padding and Trimming in SQL Databases

SQL Drivers Padding and Trimming and String Compares

SQL database servers usually have char and varchar column types for storing string data.

  • char and varchar types behave differently when it comes to padding and trimming spaces and in-string compares. Behavior may vary between char and varchar and also between different SQL database systems.
  • char types are stored padded with spaces in the database. In a char(10) column, for example, when writing ‘Stephen’, it will be stored as ‘Stephen ' (3 padding spaces) in the database.
  • varchar types are not space padded in the database. In a varchar(10) column, when writing ‘Stephen’, it will be stored as ‘Stephen’ (7 chars). When writing ‘Stephen ' (2 padding spaces, 9 chars), it will be stored as ‘Stephen ' (2 padding spaces, 9 chars).

DataFlex Embedded Database Padding and Trimming

The DataFlex embedded database stores string data in DF_ASCII and DF_TEXT type columns.

  • DF_ASCII columns are always space padded. Its behavior is like SQL char type.
  • DF_TEXT columns are not space padded. Its behavior is like SQL varchar type.

Padding and Trimming in SQL Drivers

Tables accessed through the SQL drivers have a DataFlex type (DF_ASCII, DF_TEXT) and a backend native type (char/varchar).

  • The native type of DF_ASCII columns can be either char or varchar.
  • The native type of DF_TEXT columns will usually be varchar, or sometimes varchar(max) or text.

Padding and Trimming Behavior in DataFlex SQL Drivers

(Applies to DataFlex 2021 and later. Earlier versions behave differently.)

Values in DF_ASCII/char Columns

  • Will be stored with padding spaces.
  • Will be returned with padding spaces.

For example, if SalesPerson.Id is DF_ASCII/char(4):

Move 'MM' To SalesPerson.ID

will be stored as ‘MM '.

Values in DF_ASCII/varchar Columns or DF_TEXT/varchar Columns

  • Will not be padded or trimmed.

For example, if SalesPerson.Id is DF_ASCII/varchar(4):

Move 'MM' To SalesPerson.ID

will be stored as ‘MM'.

In some SQL databases, it may be desirable to trim varchar values. In this case, the TRIM_VARCHAR_VALUES setting can be used.

TRIM_VARCHAR_VALUES can be specified in the driver configuration file (mssqldrv.int, db2_drv.int) or, when using odbc_drv, in the database-specific configuration file (MySQL.int, MariaDB.int, PostgreSQL.int, etc.).

TRIM_VARCHAR_VALUES can also be set at runtime with the following attributes:

  • DF_DRIVER_TRIM_VARCHAR_VALUES
  • DF_DATABASE_TRIM_VARCHAR_VALUES

When TRIM_VARCHAR_VALUES is set to True, values in DF_ASCII/varchar columns will be trimmed when saved:

For example, if SalesPerson.Id is DF_ASCII/varchar(4):

Move 'MM' To SalesPerson.ID

will be saved as ‘MM'.

Conversion from DataFlex Embedded to SQL Varchar

The SQL conversion wizard in DataFlex Studio and Database Builder has an option to trim varchar values during conversion. This prevents embedded DF_ASCII columns (space padded) from being converted to space-padded SQL varchar columns.

PAD STRING and NOPAD Compares

When comparing two strings, there are basically two methods:

PAD STRING Comparison

  • When two strings are of unequal length, the shortest is padded with spaces to the length of the longest, and then compared.
  • Trailing spaces are ignored when comparing strings.

For example, when comparing ‘MM’ with ‘MM ' the strings will be equal.

NOPAD String Comparisons

  • In a NOPAD comparison, no space padding takes place during the compare, so trailing spaces are relevant in comparisons.

For example, when comparing ‘MM’ with ‘MM ' the strings will NOT be equal.

Compare Methods in Various Databases

SQL databases can use either PAD STRING or NOPAD string compares:

  • The DataFlex language uses PAD STRING compares.
  • The DataFlex embedded database uses PAD STRING compares.
  • SQL Server uses PAD STRING compares.
  • DB2 uses PAD STRING compares.
  • MySQL can use both methods. It depends on the chosen collation (there are PAD SPACE and NOPAD collations).
  • PostgreSQL uses PAD STRING for char type and NOPAD for varchar type.

Since the DataFlex language uses PAD STRING comparisons, it is best to also choose PAD STRING collations for SQL databases. Using NOPAD comparison in SQL databases may cause Finds to fail.

For example:

If SalesPerson.ID is varchar(4) and contains ‘MM’:

Clear SalesPerson
Move 'MM' To SalesPerson.ID
Find Eq SalesPerson By index.1

will not find the record.

When an SQL database uses NOPAD comparisons, do not use varchar in index segments; use char instead or turn on the trim_varchar_values setting.

See Also

Understanding How Connectivity Works