Ignore case in SQL select

Hi Gurus,
We have and Upper and a Lower function.
Is there any function or way in which I can issue a select while ignoring the case in a where clause ?

I do not wish to use 'like'.

Thanks!

You don't need to use "like" for case insensitiveness.
LOWER and UPPER are the functions for these.
Is there any reason why you won't want to use these functions?

- Rajeev

Hi Rajeev,
Suppose I want all names beginning with K or k
I can say name like 'k%' and name like 'K%'.

But suppose several other alphabets are present, some in upper and some in lower in the result data set.

Then how do I get this data?

Cant I do it through a single select/where?

Hi,

You can create InterMedia index on the column and Use
CONTAINS in WHERE clause.

HTH

np70

Hi np70,

Thanks for the info. Where would I get information on creating an intermedia index? I use 8.1.6

Thanks again.

may be I am missing the point but for your query I would use --

WHERE UPPER(name) LIKE 'K%'

This, however will not make use of the index because of the function call on the column itself. But it will return what you want? won't it ?

- Rajeev

Hi Rajeev,

The point is this.

Suppose I have two names in a field Name.

And they are stored as
KriSHnA
KriShNa


I want to select both these in the same query.

WHERE UPPER(name) LIKE 'KRISHNA%'

or

WHERE UPPER(name) = 'KRISHNA'

Hi

Are you looking for something like this

select ename from emp where upper(ename) like upper('KriS%');

here upper fuction will convert everything to upper case

I Hope this is what you are looking for

Regards

Hi gurus,
Thanks for the inputs.

But if you extrapolate the example I gave...say my field has values of

Krishna
KRishna
KRishnA
kRiSHNa
KriSHNA
KRISHNa
KRISHNA
KriShNa

You can imagine how many combinations I have to use.
Of course as Rajeev mentioned, I could use a combo of lower and upper.
I just wanted to know if there is any way in which I could do it without using upper and lower.

Thanks.

Hemant,
you have to use only one combination with Upper or Lower.

select name from name_table
where UPPER(name) = ' KRISHNA';

will return ALL the rows --

name
-----------
Krishna
KRishna
KRishnA
kRiSHNa
KriSHNA
KRISHNa
KRISHNA
KriShNa

- Rajeev

Yes Rajeev,
Got it.

Thanks a deal.

the following will return all the values you specified.

select ename from emp where lower(ename) like 'krishn%';

If you want this statement to use an index, you have to create a function based index.

Robel