Case and Width Insensitive Matching for SQL Queries about doma HOT 6 CLOSED

Thank you for your proposal.

Could you tell me which RDBMS you are using and what kind of SQL you would like to execute?

Some RDBMS provide a feature called collation, which might meet your needs.
https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver16

from doma.

Thank you for sharing the information.

I think creating a custom function would be suitable in your case.

1. Create a class that inherits from OracleExpressionFunctions and write a method that generates the string to be passed to the pattern argument of the REGEXP_LIKE function.

package example:

public class MyFunctions extends OracleExpressionFunctions {
  public String insensitive(String text) {
    return ...;
  }
}

2. Pass an instance of the above MyFunctions to the constructor of OracleDialect.

OracleDialect dialect = new OracleDialect(new MyFunctions());

3. Specify the fully qualified name of the above MyFunctions in the options for annotation processing (the following example is when writing in build.gradle.kts).

tasks {
    compileJava {
        options.compilerArgs.addAll(listOf("-Adoma.expr.functions=example.MyFunctions"))
    }
}

4. Call the method defined in MyFunctions from the SQL template.

SELECT * FROM users
WHERE REGEXP_LIKE ( users.name, /* @insensitive(name) */'[[=t=]][[=a=]][[=r=]][[=o=]]')

See also https://doma.readthedocs.io/en/latest/expression/#using-custom-functions.

from doma.

No, there are no plans to do so at this time.
There are two reasons for this:

1. As you mentioned, it is challenging to accommodate different RDBMS.
2. Even if we focused on a specific RDBMS, we believe it would be difficult to write a generic logic that meets the needs of many users.

If you try out a custom function and it works well, I would be happy if you could share that function with us. If many users agree with that function, we might consider incorporating it into Doma.

from doma.

Thank you for your response and for considering my feature request.

Could you tell me which RDBMS you are using and what kind of SQL you would like to execute?

I am currently using Oracle.
Here's an example of the kind of SQL I would like to execute:

SELECT * FROM users
WHERE REGEXP_LIKE ( users.name, '[[=t=]][[=a=]][[=r=]][[=o=]]')

from doma.

@nakamura-to
Thank you for your response, despite it being a day off.

Is there any plan to include a custom function named @insensitive in the next update? I understand it might be challenging given the need to accommodate different RDBMS.

from doma.

Understood.
Since this issue has been resolved, I will go ahead and close it.
Thank you very much for your assistance.

from doma.