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

Hi Jon,

I would suggest the following:

select * from table2 where instr(lower(description),'oracle') >
0;

lower will make all the characters in the description field lowercase
so you only need to test for lower case characters.

instr will return > 0 if it finds any occurance of the word 'oracle'

Hope this helps,
C

Hi Jon,

I would suggest the following:

select * from table2 where instr(lower(description),'oracle') >
0;

lower will make all the characters in the description field lowercase
so you only need to test for lower case characters.

instr will return > 0 if it finds any occurance of the word 'oracle'

Hope this helps,
C

Hi Jon,

I would suggest the following:

select * from table2 where instr(lower(description),'oracle') >
0;

lower will make all the characters in the description field lowercase
so you only need to test for lower case characters.

instr will return > 0 if it finds any occurance of the word 'oracle'

Hope this helps,
C

Hi Jon,

I would suggest the following:

select * from table2 where instr(lower(description),'oracle') >
0;

lower will make all the characters in the description field lowercase
so you only need to test for lower case characters.

instr will return > 0 if it finds any occurance of the word 'oracle'

Hope this helps,
C

On Feb 18, 3:44*pm, jharbo... (AT) googlemail (DOT) com wrote:
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

On Feb 18, 3:44*pm, jharbo... (AT) googlemail (DOT) com wrote:
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

On Feb 18, 3:44*pm, jharbo... (AT) googlemail (DOT) com wrote:
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

On Feb 18, 3:44*pm, jharbo... (AT) googlemail (DOT) com wrote:
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