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.

 

DataFlex Embedded Database Padding and Trimming

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

 

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

 

Padding and trimming behaviour in DataFlex SQL drivers:

(Applies to DataFlex 20 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.

 

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

What's New in DataFlex 2021