When using the MS ODBC Text Driver to query text files for data comparison, the queries are case-sensitive. This is particularly noticeable in join operations or any conditional statements where there might be data inconsistencies.
In general, the 32-bit ODBC drivers offer an option to select the “Microsoft Text Driver,” whereas the 64-bit ODBC drivers provide the “Microsoft Access Text Driver” option. Microsoft changed the driver names with the 64-bit ODBC drivers, starting with the Microsoft Access Database Engine 2010 and 2016 Redistributable versions. After upgrading from SmartConnect 2018 to SmartConnect 2021 or later versions, changes in driver names could affect the way you build queries if you have any.
In summary, the Microsoft Text Driver is included in the 32-bit ODBC Text Driver and is compatible with SC 2018. On the other hand, the Microsoft Access Text Driver is part of the 64-bit Access Database Engine and is compatible with SC 2021.
For Example:
For instance, if you have a field named “Batch Number” in a text file that contains the uppercase value “Tax”, executing the query:
WHERE [BATCHNUMBER]= 'tax'will not return any results.
To resolve the issue, you need to use the ODBC function.
Here, you’ll find a comprehensive list of functions that you can use as needed.
For this example, the ODBC function LCASE converts strings to lowercase. By also converting the search string to lowercase, you will achieve a virtually case-insensitive query.
Have a question? Please reach out to us at support@eonesolutions.com
