Re: Case insensitive when using LIKE in SQL statement-not use LOWER or UPPER

Oracle requires it for a reason. In many systems, espically unix, upper and lowercase words are different. In our stepup some things have to be upper and others lower. I want to be able to get just one or other not both. i see it as a feature.
In article <6qoo2j$5k9$1_at_nnrp1.dejanews.com>, bonanos_at_yahoo.com wrote:
>Hi
>
>
>> I can do that with SQL Server.
>>
>> SELECT * FROM emp WHERE name like 'TOM%';
>>
>> Steve Gioberti wrote in message
>> <6qfhm4$43s$1_at_pheidippides.axion.bt.co.uk>...
>> >How about:
>> >SELECT * FROM emp WHERE upper(name) like 'TOM%'
>> >Or
>> >SELECT * FROM emp WHERE upper(name) like upper('tom%')
>> >
>> >Joel Bergeron wrote in message <6qf1d9$cc6$1_at_news.quebectel.com>...
>> >>Is it possible with Oracle 7.3 to query de database without using UPPER or
>> >>LOWER in a query like this :
>> >>
>> >>SELECT * FROM emp WHERE name like 'tom%';
>> >>
>> >>I would like to retreive all record contening :
>> >>tom, TOM, tOm,... in my result.
>
>If you stored all the values in the field in uppercase when you insert the
>values. then you could do this
>
>where name like upper('Tom%');
>
>1. The benefit of doing it this way is that you do not do full table scans.
>
>2. If in your query you say where UPPER(name) like upper('Tom%') then you will
>force a full table scan.
>
>Tough, but those are the breaks. What it forces you to do is provide the dat
>conversion functionality at the data entry point. Also I suppose if you want
>to format the data for display purpose.
>
>
>
>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

Dan Ramriez

ramdan_at_mailexcite.com
Development Analyst
Information Systems
Commonwealth Edison

The contents of this message express only the sender's opinion. This message does not necessarily reflect the policy or views of my employer, Commonwealth Edison. All responsibility for the statements made in this Usenet posting resides solely and completely with the sender. Received on Thu Aug 13 1998 - 15:50:48 CDT