Ignore case in SQL select

[hemanr]

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!

[rsuri]

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

[hemanr]

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?

[np70]

Hi,

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

HTH

np70

[hemanr]

Hi np70,

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

Thanks again.

[rsuri]

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

[hemanr]

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.

[rsuri]

WHERE UPPER(name) LIKE 'KRISHNA%'

or

WHERE UPPER(name) = 'KRISHNA'

[santoshym]

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

[hemanr]

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.