| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Ignoring Case in String Searches
Somehow, you need to normalize your data to a common case, either
lower or upper. You could create a trigger on your table, which will
convert the INSERTing or UPDATing string to uppercase. For example:
Table created.
SQL> CREATE OR REPLACE TRIGGER iu_foo BEFORE INSERT OR UPDATE OF text
ON foo REFERENCING OLD AS OLD
NEW AS NEW FOR EACH ROW
2 BEGIN
3 :new.text := UPPER(:new.text);
4 END;
5 /
Trigger created.
SQL> INSERT INTO foo VALUES(1, 'Oracle');
1 row created.
SQL> SELECT * FROM foo;
ID TEXT
--------- --------------------
1 ORACLE
When you perform your query, convert the criteria to uppercase (you can do this in PowerScript using the Upper() function). If you convert the criteria and *not* the column, your query should be able to use the index on this text column.
Hope this helps.
On Wed, 13 May 1998 02:50:19 GMT, "Steve Emmons" <semmons_at_net.bluemoon.net> wrote:
>We are creating a powerbuilder application that has string searching
>functionality. i.e. looking up a company or person in an address book. The
>problem we have is that we are trying to make a database independent
>application and therefore don't want to use the Upper() function in the SQL
>statement since the SQLAnywhere (SQLAnywhere is not case sensitive)database
>doesn't understand this function. Also using the Upper function seems to
>eliminate the use of the Index on the name column.
>
>Is there a way that we can make the database case insensitive. We where
>told by Oracle Support that this could not be done so our thought was to
>make another name column that was in all uppercase and we could use that to
>do the searching.
>
>Any other/better ideas?
>
>We are running 7.3.4 on NT 4.0
>
>Thanks
>
>Steve
>
>
Thanks!
Joel
Joel R. Kallman Oracle Government, Education, & Health
Columbus, OH http://govt.us.oracle.com jkallman@us.oracle.com http://www.oracle.com
 |
 |