Select query for case insensitive

l.nethaji · Posted: Mon Jun 28, 2010 2:19 pm

Hi,

I have to use a select query for case insensitive where clause.

say i have table t1 which has name in it

T1
Name

Nethaji
NETHAJI
NEthaji
NEThaji.

If i try to give the query

Select name from t1 where name = 'nethaji'.

I need to get all the 4 values regardless of the case.

I hope the DB2 system takes all these names as different values.

Please let me know if any other option is possible.

Thanks,
L.Nethaji

akatast · New User Joined: 05 Mar 2009 Posts: 15 Location: Ohio

Have you tried with "LIKE"?

Thanks.
Akatast

GuyC · Posted: Mon Jun 28, 2010 4:25 pm

Let see: What can be a function to use when we need uppercase/lowercase ?
UCASE() ? UPPERCASE() ? UPPER() ?

Maybe open a manual and see what is available.

ssmukul · New User Joined: 22 Jan 2008 Posts: 19 Location: India

Hi,

You can use below query,
Select name from t1 where lcase(name) = 'nethaji'

l.nethaji · Posted: Thu Jul 08, 2010 5:10 pm

Hi GUYC,

Ya we can use UPPER and lower case function and we can retireve it

Using

Select UPPER(name) from table T.

This will fetch me the result set .

NETHAJI
NETHAJI
NETHAJI
NETHAJI.

But i have a requirement where my sensitivity must be same having a string nethaji.

If I want to get the result set with right sensitivity :
In my query i want to use all 7! probability cases for the name Nethaji in the where clause using or clause.But this is cumbersome process.

IS there any way we can do it .

Regards,
L.Nethaji

Craq Giegerich · Posted: Thu Jul 08, 2010 5:20 pm

l.nethaji · Posted: Thu Jul 08, 2010 5:58 pm

Hi craq,

Thanks a Lot .

I must have used UPPER which is specified in the previous post by GUyc.

Thanks Craq and Guyc.

regards,
L.Nethaji

delago · New User Joined: 29 Jul 2005 Posts: 21 Location: Brazil

Hi guy's.

So...using to advantage the subject, when I use UCASE in the where clause the performance of my query can be low. This afirmation is true?

Now I have a problem with performance in a stored procedure in DB2 and it use this command in the where clause.

I tks all help.

Fernando Delago - Brazil/SP

GuyC · Posted: Wed Jan 19, 2011 9:43 pm