pm04@bunny.UUCP (Preeti Mehta) (02/14/89)
Does anyone out there know of a way to perform case-insensitive string matching on character strings defined as indexes on an Oracle table? I am a new user, and have so far read parts of the DBA guide, and the SQL*Plus Users guide. As far as I know, you have to know how the indexed string value was stored (upper or lower case), in order to get a match in a query to the database table. Is this correct, or am I missing something? Are there any clever tricks one can use to overcome this problem? -- Preeti Mehta, GTE Labs., Waltham, MA e-mail : pm04%gte-labs.csnet@relay.cs.net
jan@grebyn.COM (Jan Baron) (02/15/89)
An easy way to get around this is as follows:
select * from tablename where upper(columnname) = upper(comparestring);
If you upper (or lower) both sides of the comparison, then you don't
have to worry about case. This only affects the comparison itself.
It doesn't change the database values.
I hope this is of some help.
--
***********************************************************************
* ...another day, another quarter... * Jan A. Baron (jan@grebyn.com) *
* (inflation, y'know) * ...!{uunet,hadron}!grebyn!jan *
***********************************************************************eric@pyrps5 (Eric Bergan) (02/15/89)
In article <6555@bunny.UUCP> pm04@bunny.UUCP (Preeti Mehta) writes: > >Does anyone out there know of a way to perform case-insensitive string matching >on character strings defined as indexes on an Oracle table? > >I am a new user, and have so far read parts of the DBA guide, and the SQL*Plus >Users guide. As far as I know, you have to know how the indexed string value >was stored (upper or lower case), in order to get a match in a query to the >database table. Is this correct, or am I missing something? Are there any >clever tricks one can use to overcome this problem? The usual solution that I know of, assuming you want to make sure you use the index, is insist that the field you are interested in is stored in either upper or lower case, and they convert the searched for string to the appropriate case. Do not do something like where uppercase(searchfield) = uppercase(searchstring) where searchfield is the indexed value. If it is not already stored uppercase, it will not be able to use the index. Of course, if you want to be able to display the database field with "proper" mixing of upper and lower case, it means you have to have two fields in the table. One in mixed upper and lower case, only for display. The other forced to uppercase, and with the index built upon it. Very common for tables containing names. Also allows you to strip punctuation, spacing, etc from the searched field, so no concerns over "van Johnson" and "Vanjohnson". eric ...!pyramid!eric
jkrueger@daitc.daitc.mil (Jonathan Krueger) (02/16/89)
In article <6555@bunny.UUCP>, pm04@bunny (Preeti Mehta) writes: >Does anyone out there know of a way to perform case-insensitive string >matching on character strings defined as indexes on an Oracle table? Why yes, I know three ways. They're all awful. 1) give up the indexing, perform exhaustive searches like uppercase(table.column) = "SEARCHSTRING" or table.column = "[Ss][Ee][Aa][Rr][Cc][Hh][Ss][Tt][Rr][Ii][Nn][Gg]" Clearly, if you need to do this often or on large tables, performance will be unacceptable. It's also awkward and error-prone, and must be coded into each application. In effect, it's not a solution at all, it's very nearly giving up on the problem. (If you were to decide to case fold data values before storing them, that would be giving up). 2) maintain a case-folded partner column (implemented by triggers if available, otherwise uglier methods), express search as table.partner_column = "SEARCHSTRING" index on the partner column, not the real one. Search on the partner, return the contents of the real column. This is how I implemented full-text searching using a standard relational engine. This is also how an unannounced (last I heard) Oracle product works. Highly subvertible, costs extra constant space and time, and implementation for detail tables is too hideous to describe. Also, since not known by the relational engine, query optimizer misses many opportunities for optimization. In effect, uses a RDBMS but abandons most of the advantages of the relational model. 3) directly manipulate internals of index tables to fold case. Avoids space and time costs of method 2, but highly non-portable: will work neither on other vendors' RDBMS nor on future versions of own RDBMS. Not only highly subvertible, this IS a subversion. Total kludge, blatant example of trying to turn the system into something it isn't. I also know a fourth way that's clean and workable, and answers the next request you'll make: efficient substring searching. I don't know any vendors who support it. 4) Employ user-defined transforms on data values before generating index values. For instance, your problem would be solved with a simple transform: index on uppercase(table.column) is indextbl This would indicate to the relational engine that inserts and updates to this column should generate an index value on the case folded data value. The query optimizer knows to use the index in one of three ways: A. automatically and silently case fold qualifications that select against this column (perhaps with syntax to override for queries intended to be case sensitive; could maintain second ordinary index to speed both types of queries) B. spot qualifications that use the same transform C. require override syntax: avoids aliasing problems with method B but probably not syntactically compatible with standard SQL, as method A could be. In short, with user defined transforms on index generation, it's simple, clean, efficient, non-subvertible, general, and safe. I know of no vendor who supports them. If vendors were to become convinced that we consider this important, and that we plan to buy products that do support it, this may change. -- Jon --
gupta@cullsj.UUCP (Yogesh Gupta) (02/16/89)
In article <11813@grebyn.COM>, jan@grebyn.COM (Jan Baron) writes: > An easy way to get around this is as follows: > > select * from tablename where upper(columnname) = upper(comparestring); > But if you have an index on columnname, you would not use it, which was the original poster's question. -- Yogesh Gupta | If you think my company will let me Cullinet Software, Inc. | speak for them, you must be joking.