I am using a native connector in ODC to query from a Snowflake database. How do I write my filter to be case-insensitive? ODC doesn't have a clause akin to SQL's ILIKE, and LIKE is not case-insensitive when filtering data from an external database.
Hello @Vikram Navale
If you need to perform case-insensitive operations in a Snowflake database, the easiest solution (from the point of view of the developer) would be to use collations at the database or table level. This is also the recommended approach to improve performance.
If that's not possible, I think at least for now, your options are limited. The reason is that when querying external databases, the platform uses ANSI SQL.
But you can use SQL to connect to the external database connected with Data Fabric since last year.
You can see here what you have available.
I think the solution would be to use UPPER or LOWER on both operands when searching/filtering. But this may (will?) have negative impact on the performance.
Cheers!
You can make the filter case-insensitive by converting both the database value and the input value to the same case using Upper() or Lower(). Since ODC does not support ILIKE for external databases like Snowflake, you can use something like Upper(ColumnName) LIKE "%" + Upper(SearchText) + "%". This ensures the comparison ignores case differences while filtering data through the Native Connector.
I brought up collation with the manager whose team oversees the database. I haven't yet seriously looked at Data Fabric. I am using ToUpper(), for now, while someone else decides if collation makes sense for us.
Thank you!
you can use Index() in ODC instead of LIKE if your goal is a contains search.
For case-insensitive searching, convert both values to the same case:
Index(Upper(Entity.Name), Upper(SearchText)) > 0