Re: help searching for oracles and Oracles and ORACLES

On Feb 18, 3:44 pm, jharbo..._at_googlemail.com wrote:
> Hello Mark D Powell, Robert Klemme, Michael O'Shea, David FitzJarrell
> and thank you for your help before.
>
> I have a table called "table2" that has the following in it
>
> id1      Description
>  1       oracle oracle oracle oracle
>  2       oracle school oracle school school school
>  6       the school the party the oracle
>
> How can I get SQL to get rows with Oracle or oracle (first letter is
> upcase) or ORACLE
>
> My SQL is
>
> select * from table2 where description like '%oracle%' or description
> like '%Oracle' or description like '%ORACLE%'
>
> In access '%oracle%' works in upcase or lower case but in Oracle I
> have to keep testing for all cases. The word might be oRaCLe but I
> don't want to write them all.
>
> How can I do this
>
> Thank you
>
> Jon

SQL> create table table2 (
  2 id number,
  3 description varchar2(50));

Table created.

SQL> insert all
  2 into table2 values (1,'oracle oracle oracle oracle')   3 into table2 values (2,'oracle school oracle school school school')
  4 into table2 values (3, 'Oracle is the oracle for Oracle')   5 into table2 values (4, 'OrAcLe is less of an oRACLE than oRaClE ever was')
  6 into table2 values (5, 'well, gee, my duck needs new shoes')   7 into table2 values (6,'the school the party the oracle')   8 select * from dual
  9 /

6 rows created.

SQL> commit;

Commit complete.

SQL> select *
  2 from table2
  3 where instr(upper(description), 'ORACLE') > 0   4 /

        ID DESCRIPTION

---------- --------------------------------------------------
         1 oracle oracle oracle oracle
         2 oracle school oracle school school school
         3 Oracle is the oracle for Oracle
         4 OrAcLe is less of an oRACLE than oRaClE ever was
         6 the school the party the oracle

SQL> David Fitzjarrell Received on Wed Feb 20 2008 - 14:46:14 CST