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. Behaviour may be different 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 behaviour is like SQL char type.
• DF_TEXT columns are not space padded. Its behaviour 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 behaviour 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.

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.

If SalesPerson.Id is DF_ASCII/varchar(4)

Move ‘MM’ To SalesPerson.ID

will be stored as ‘MM‘

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:

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), to be 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 2 strings are of unequal length, the shortest is padded with spaces to the length of the longest, and then compared.

Or put another way: Trailing spaces are ignored when comparing strings.

So 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.

So 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 collate (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 chose PAD STRING collations for SQL databases.

Using NOPAD comparison 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