case insensitive column data search

case insensitive column data search

  
Hi,
When running query in OS observed that, it is doing case insensitive column data query. I mean, in my table i have records with all capital letters in a column. when i do a select query on that table with that column in where clause, eventhough i give where clause values in small case, the query is fetching the data. Why this is happening, if i want to do the exact case seach what should i do.

eg: Entity E1 has columns c1 and c2, c1 column has all the data in capital letters. When i write query with condition c1=inparam1 and if i pass the inparam values in small case still records are shown of capital case



I am using java platform with Oracle and OS v8.
Thanks,
Thiru
Hi Thiru

I do not know what your particular use case is, but as you indicate the OutSystems Platform is, by default, Case and Accent insensitive.
There are some ways you can do a search for case sensitive, depending on what you are doing:

  1. You can first do a query returning lower and uppercase results, and inside the platform filter for a specific case (with a for-each and using the platform's text compare functions);
  2. If the result set must come filtered from the database, you can use an advanced query and do two filters:
    - First use the case insensitive filter (e.g. c1 = inparam1) to use the indexes and speed up the query;
    - Use a construct like NLSSORT to do the case sensitive compare - e.g. NLSSORT(c1, 'NLS_SORT = BINARY') = NLSSORT(inparam1, 'NLS_SORT = BINARY').
For more information on NLSSORT, check out http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions099.htm

Regards,
Acácio