| 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: Case insensitive/ sensitive
On Tue, 25 Aug 1998 07:28:13 GMT, sevinge_at_my-dejanews.com wrote:
> Hi, friends!
> We have done a migration from SQL Server to Oracle 7.3.
>
> One problem we cannot resolve is the string comparison, because SQL
>Server doesn't distinct letter case. By default, Oracle distingues the
>case. We looked for a 'set variable' but this donīt work what we want.
>The sql-case variable only permits those three values: lower, upper, mixed,
>but not the INSENSITIVE CASE.
>
> Anyone know how to do this? Thanks .
If you normalize your data, convert it all to a common case, you can apply the UPPER or LOWER function to your *criteria* and not the column.
E.g., if the criteria is "Joel", you could do
SELECT fname FROM emp WHERE fname = UPPER('Joel')
If, instead, you did
SELECT fname FROM emp WHERE UPPER(fname) = UPPER('Joel')
you will first be applying the UPPER function to *all* the values of the fname column.....full-table scan, which can most times be a bad thing.
If you need to preserve the case of the original column value, you can create a "shadow" column, populated via a trigger, which contains the normalized data.
>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
BTW....I answered this same question in this same newsgroup on 8/21/1998. Sometime check out the Usenet archive DejaNews at http://www.dejanews.com. Chances are someone has already asked and received an answer to your question.
Hope this helps.
Thanks!
Joel
Joel R. Kallman Oracle Government, Education, & Health
Columbus, OH http://govt.us.oracle.com jkallman@us.oracle.com http://www.oracle.com
 |
 |