UPPER and LOWER functions in Informix DB LIKE queries do not work with all entries in the table

Background information:

• IBM Informix Dynamic Server Version 12.10.FC14WE
• DB_LOCALE=et_EE.utf8
• Table has an ID primary key, no indexes
• Column datatype lvarchar (-1)

The table column has names like “FIRST COMPANY”, “SECOND COMPANY”, “Third”. To find name matches the UPPER function is being used. Example of the problem:

• SELECT * FROM companies WHERE UPPER(name) LIKE 'FIRST%'; – gives no results
• SELECT * FROM companies WHERE UPPER(name) LIKE 'SECOND%'; – gives expected result (SECOND COMPANY)
• SELECT * FROM companies WHERE name LIKE 'FIRST%'; – gives expected result (FIRST COMPANY)
• SELECT * FROM companies WHERE UPPER(name) LIKE 'FIRST%' OR name LIKE 'FIRST%' – gives no results

What could cause this kind of behavior and how to solve it? Maybe entries in the same database table differ in some way?

Answer

As mentioned in the comments of the question there is likely some sort of data corruption that can be fixed with deletion and re-creation of the problematic entries (updating had no effect). Since the system around this is rather complex and involves legacy technologies we did not dive deeper to figure out the root cause. Due to some other needs and considerations we instead created a separate column where company names are always in uppercase and adjusted necessary select queries to use that without the UPPER function.