Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 1278
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: 1278
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts Summing on Mixed Case Literals Roy Ware SYNCSORT 3 Tue Jan 03, 2017 10:18 pm
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us