What's the most efficient way to do a case-insensitive like expression?

Question

In Pervasive v13, is there a "more performant" way to perform a case-insensitive like expression than is shown below?

select * from table_name
where upper(field_name) like '%TEST%'

The UPPER function above has performance cost that I'd like to avoid.

I disagree with those who say that the performance-overhead of UPPER is minor; it is doubling the execution time compared to the exact same query without UPPER.

Background:

I was very satisfied with the execution time of this wildcard-like-expression until I realized the result set was missing records due to mismatched capitalization.

Then, I implemented the UPPER technique (above). This achieved including those missing records, but it doubled the execution time of my query.

This UPPER technique, for case-insensitive comparison, seems outlandishly intensive to me at even a conceptual level. Instead of changing a field's case, for every record in a large database table, I'm hoping that the SQL standard provides some type of syntactical flag that modifies the like-expression's behavior regarding case-sensitivity.

From there, behind the scenes, the database engine could generate a compiled regular expression (or some other optimized case-insensitive evaluator) that could well outperform this UPPER technique. This seems like a possibility that might exist.

However, I must admit, at some level there still must be a conversion to make the letter-comparisons. And perhaps, this UPPER technique is no worse than any other method that might achieve the same result set.

Regardless, I'm posting this question in hopes someone might reveal a more performant syntax I'm unaware of.

Answer 1

You do not need the UPPER, when you define your table using CASE.

The CASE keyword causes PSQL to ignore case when evaluating restriction clauses involving a string column. CASE can be specified as a column attribute in a CREATE TABLE or ALTER TABLE statement, or in an ORDER BY clause of a SELECT statement.

(see: https://docs.actian.com/psql/psqlv13/index.html#page/sqlref%2Fsqlref.CASE_(string).htm )

CREATE TABLE table_name (field_name VARCHAR(100) CASE)