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

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 Received on Tue Aug 11 1998 - 01:24:19 CDT