Home » SQL & PL/SQL » SQL & PL/SQL » Mixed case search in oracle 9i
Mixed case search in oracle 9i [message #316067] Wed, 23 April 2008 11:23 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I need to find out to how to retrieve values if user entered in Mixed case means (case insesitive)?
We don't have control when user enters values and it might be SMITH or Smith or smith or SmIth.
How can write the query to get this kind of mixed case data?

Thanks,
Re: Mixed case search in oracle 9i [message #316074 is a reply to message #316067] Wed, 23 April 2008 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about LOWER and UPPER functions?

Regards
Michel
Re: Mixed case search in oracle 9i [message #316076 is a reply to message #316074] Wed, 23 April 2008 12:09 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks Michel.
IS it work with mixed case liek "SmItH"?
In my query i should use with OR condition then?

select name from emp where emp = LOWER(emp) or emp = UPPER(emp)?

Thanks,
Re: Mixed case search in oracle 9i [message #316078 is a reply to message #316076] Wed, 23 April 2008 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you want to achieve or find?
Find all rows where there is mixed case?

Regards
Michel
Re: Mixed case search in oracle 9i [message #316081 is a reply to message #316076] Wed, 23 April 2008 12:53 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
I think we need the answer to Michel's question first, but I am going to assume that want you want is to just move the UPPER or LOWER to the left side of the where clause.
Re: Mixed case search in oracle 9i [message #316082 is a reply to message #316078] Wed, 23 April 2008 12:56 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks once again.
I need to find out either upper or lower or mixed case values as user might enter like 'SMITH' or 'Smith' or 'smith' or 'smiTH' or 'SmItH' or any mix letter.

Regards,
Re: Mixed case search in oracle 9i [message #316085 is a reply to message #316082] Wed, 23 April 2008 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So compare, for instance, the lower value in your column to the lower input value.

Regards
Michel
Re: Mixed case search in oracle 9i [message #316086 is a reply to message #316085] Wed, 23 April 2008 13:14 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks.
So you mean to write the query like:

select empno from emp where ename = LOWER(ename) or ename = UPPER(ename)or ????

Re: Mixed case search in oracle 9i [message #316089 is a reply to message #316086] Wed, 23 April 2008 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No this is not what I meant.
You didn't say us what is the input. A value entered by a client or something? Where does come 'smith' or...? Do you want all the related to 'smith' (whatever is the case)?

Regards
Michel
Re: Mixed case search in oracle 9i [message #316090 is a reply to message #316067] Wed, 23 April 2008 13:32 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> I need to find out either upper or lower or mixed case values as user might enter like 'SMITH' or 'Smith' or 'smith' or 'smiTH' or 'SmItH' or any mix letter.
> select empno from emp where ename = LOWER(ename) or ename = UPPER(ename)or ????

No. Where do you notice the searched name ('smITh')? Do you not think, it would be useful to somehow introduce it into the query?

Think. What is the result of LOWER('sMIth')? And LOWER('SmiTh')? And LOWER('smitH')? And ...?
How will you transform ENAME to be always in lowercase?
If you do not know, have a little break and return to the beginning of this paragraph.
Otherwise, simply compare these two values.
icon6.gif  Re: Mixed case search in oracle 9i [message #519872 is a reply to message #316090] Wed, 17 August 2011 05:56 Go to previous messageGo to next message
samrose
Messages: 1
Registered: August 2011
Location: chennai
Junior Member
if u want to search characters of mixed case,

eg: SmiTh

U can type your query as follows:

Select * from t_name_mast where f_name != upper(f_name) and f_name != lower(f_name);


This way, you can retrieve only the data which has mixed case.

And then if you want to update it as bulk, u can copy the data from the command prompt, create an excel with your bulk query and update all at once as either lower case or upper case.

Good evening! Take care!
Re: Mixed case search in oracle 9i [message #519884 is a reply to message #519872] Wed, 17 August 2011 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good evening? After 3.5 years? I hope the sun goes down and up several times since OP posted.
It is nice you try to help people but please search for topics that are younger and not already answered.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Regards
Michel
Re: Mixed case search in oracle 9i [message #519895 is a reply to message #316086] Wed, 17 August 2011 08:19 Go to previous messageGo to next message
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
Member

Just try this
select empno from emp where lower(ename) = LOWER(ename)

In the above query, in which way the user enters it will convert into lower case and compare the same in emp table,as we specified lower function in emp table also, it will give proper result
Re: Mixed case search in oracle 9i [message #519897 is a reply to message #519895] Wed, 17 August 2011 08:29 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This topic is 3.5 years old and is over.
In addition: "lower(ename) = LOWER(ename)" is always true unless ename is null.

Regards
Michel


Previous Topic: Oracle equivalent SQL for this basic SQL query?
Next Topic: Populating a Ref Cursor from a Collection
Goto Forum:
  


Current Time: Thu Dec 08 00:04:04 CST 2016

Total time taken to generate the page: 0.05716 seconds