Case in-sensetive searches

Author: sbpatel48@gmail.com (Sandip)

Hi, We are using Oracle, and I'd like to carry out case in-sensitive retrieves. I've got part of the way there, by using : sql "ALTER SESSION SET NLS_COMP=ANSI","DEF" sql "alter session set NLS_SORT=BINARY_CI","DEF" However, this only works correctly if the complete text is typed, and doesn't appear to work if wildcard * is used. ie Value of the db 'TEST' if i type 'test' and retrieve i get back the 'TEST' record. Type 'TES*', i get the 'TEST' record back but if i type 'tes*, i don't get anything back. Anyone got any ideas, pls. Sandip

4 Comments

  1. Hi Sandip, One option goes back to the old MATCHCODE: Create a view where the contents of the fields is set to uppercase during the select. Because the columns are identical to the base table, you can just copy the uniface entity; then change all the text fields syntax to UPPERCASE (!). Use this entities for your retrieves, copy the primary keys and select the records from the database table. ******************************************************************************************************************* Because looks like you only use ORACLE, one option is to build a customised WHERE option : use the oracle functions to convert the field contents to uppercase then compare this uppercase to the %%$uppercase(field)%%%. A how2 from Oracle at http://www.techonthenet.com/oracle/functions/upper.php reads: Let's say that we have a suppliers table with a field called supplier_name that contains the values TEST, Test, or test. If we wanted to find all records containing the word "test", regardless of whether it was stored as TEST, Test, or test, we could run either of the following SQL statements: select * from suppliers where upper(supplier_name) like ('TEST%'); or select * from suppliers where upper(supplier_name) like upper('test%') These SQL statements use a combination of the upper function and the LIKE condition to return all of the records where the supplier_name field contains the word "test", regardless of whether it was stored as TEST, Test, or test. (*) or follow the hints in http://stackoverflow.com/questions/1031844/oracle-db-how-can-i-write-query-ignoring-case from there I got that your settings affect only the straight compare, but not the selects with wildcards.


    Author: ulrich-merkel (ulrichmerkel@web.de)
  2. Hi Sandip, perhaps setting NLS_COMP=LINGUISTIC; will do the trick http://www.orafaq.com/node/91 mentiones: This is where the new setting of NLS_COMP comes into play. Setting NLS_COMP to LINGUISTIC tells Oracle to perform strict case insensitivity operations and looks like the following two examples. As you can see there is complete case insensitivity for both equality and now LIKE operations. SQL> alter session set NLS_SORT=BINARY_CI; Session altered. SQL> alter session set NLS_COMP=LINGUISTIC; Session altered. SQL> select name from names where name = 'Johny Jacobson'; NAME ------------------------------------------------------------------ Johny Jacobson johny jacobson JOHNY JACOBSON SQL> select name from names where name like 'Johny%'; NAME ------------------------------------------------------------------ Johny Jacobson johny jacobson JOHNY JACOBSON There are literally dozens of ways in Oracle you can pattern match and find the information you need. The problem was, for case insensitivity you couldn't rely on strict case insensitivity in its purest form. You needed to hook in other functions or expressions. Now with Oracle 10gR2 you can set the NLS_COMP parameter and get true case insensitivity without having to change your application. Nice! Especially if you needed to port to another database vendor. Just so long as their case insensitivity is the same as Oracle's.


    Author: ulrich-merkel (ulrichmerkel@web.de)
  3. Hi Ulrich, Thanks for you help, changing the session settings has resolved the problem for me. Thanks Sandip


    Author: Sandip (sbpatel48@gmail.com)
  4. Hi Sandip, thank you for the feedback that setting NLS_COMP=LINGUISTIC will do the trick in ORACLE. Perhaps this will inspire other ORA users


    Author: ulrich-merkel (ulrichmerkel@web.de)