select statement... fields in lowercase

[davejam]

hi all,

i'm trying to do a search facility but as i've found oracle is completely case sensitive....

as theres many formats of the same kind of code in the table i'd rather have in my select something that sets to lower case both the search value and the field value

nb.calling this from a web page

kinda like (in no way actual code) just for example
sSql = "SELECT myRef FROM myTable WHERE lowercase(myField) = lowercase(" & myWebSearch & ")"

thankyou for any help, tips or advice





daveJam

even my shrink says its all your f#@/ing fault

 

[SantaMufasa]

You are virtually there, Dave, with your code. Here is a code sample that queries one of my Oracle Education tables:

select last_name from s_emp;

LAST_NAME
-----------------
Velasquez
Ngao
Nagayama
Quick-To-See
Ropeburn
Urguhart
Menchu
Biri
Catchpole
Havel
Magee
Giljum
Sedeghi
Nguyen
Dumas
Maduro
Smith
Nozaki
Patel
Newman
Markarian
Chang
Patel
Dancs
Schwartz

25 rows selected.

select last_name from s_emp
where lower(last_name) like lower('%AR%');

LAST_NAME
----------------
Urguhart
Markarian
Schwartz

3 rows selected.

Let us know if this resolves your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Dagon]

If you want to avoid applying a function to the column, you can also do this:

alter session set nls_comp=ansi;

alter session set nls_sort = GENERIC_BASELETTER;

select * from (select 'TEST' as text from dual union all select 'Test' from dual union all select 'test' from dual)
where text = 'TEST';

 

[davejam]

sorry not got back to finish this thread...

yes i was nearly there, maybe should have spent that extra five minutes looking before i posted....

thankyou for the LOWER()

and i think for most uses dagon's session doctorate would be brilliant. in my case i just neede to have it for the set alias page as it comes straight from oracle and help me set customers up with their counterparts, then later i need exact as this speeds things up... so for now i don't need the session set but i will keep this in mind for any future use of oracle

thank you both

daveJam

even my shrink says its all your f#@/ing fault