Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Select query for case insensitive

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
l.nethaji

New User


Joined: 16 Mar 2008
Posts: 90
Location: tamil nadu

PostPosted: Mon Jun 28, 2010 2:19 pm    Post subject: Select query for case insensitive
Reply with quote

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
Back to top
View user's profile Send private message

akatast

New User


Joined: 05 Mar 2009
Posts: 15
Location: Ohio

PostPosted: Mon Jun 28, 2010 3:48 pm    Post subject:
Reply with quote

Have you tried with "LIKE"?

Thanks.
Akatast
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Jun 28, 2010 4:25 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
ssmukul

New User


Joined: 22 Jan 2008
Posts: 19
Location: India

PostPosted: Tue Jun 29, 2010 10:45 am    Post subject: Reply to: case insensitive
Reply with quote

Hi,

You can use below query,
Select name from t1 where lcase(name) = 'nethaji'
Back to top
View user's profile Send private message
l.nethaji

New User


Joined: 16 Mar 2008
Posts: 90
Location: tamil nadu

PostPosted: Thu Jul 08, 2010 5:10 pm    Post subject: Reply to: Select query for case insensitive
Reply with quote

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
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Jul 08, 2010 5:20 pm    Post subject: Re: Select query for case insensitive
Reply with quote

l.nethaji wrote:
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



Select name from t1
where upper(name) = upper(nethaji);
Back to top
View user's profile Send private message
l.nethaji

New User


Joined: 16 Mar 2008
Posts: 90
Location: tamil nadu

PostPosted: Thu Jul 08, 2010 5:58 pm    Post subject: Reply to: Select query for case insensitive
Reply with quote

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
Back to top
View user's profile Send private message
delago

New User


Joined: 29 Jul 2005
Posts: 21
Location: Brazil

PostPosted: Wed Jan 19, 2011 8:55 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Jan 19, 2011 9:43 pm    Post subject:
Reply with quote

delago wrote:
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

yes, it probably will be low, a possible solution is index on expression which is available in DB V9. (Info is available all over the web.)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts RFE: DB2 support for mixed case names. Pedro DB2 0 Tue Jul 04, 2017 1:32 am
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us