By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,617 Members | 1,166 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,617 IT Pros & Developers. It's quick & easy.

Query to fetch name with mixed case

P: 4
Hi All,

Can anyone of you send me a query to fetch only the name of the users who has name in mixed case (lower and upper case) .

And query to fetch only name with upper case ?

Thanks.
Nov 6 '08 #1
Share this Question
Share on Google+
11 Replies


debasisdas
Expert 5K+
P: 8,127
Can you post the query that you are working on.
Nov 6 '08 #2

P: 4
Can you post the query that you are working on.

Hi,

I hv not tried that one .....as i am not familiar with that .

Can u help.
Nov 6 '08 #3

P: 4
select * from tablename where regexp_like(name,'[A-Z]'); - I executed this query and got

ERROR at line 1:ORA-00920: invalid relational operator
Nov 6 '08 #4

P: 4
[IMG]Hello[/IMG]

Found the query:

select name from tablename where name != lower(name);


Hope this will help others
Nov 6 '08 #5

amitpatel66
Expert 100+
P: 2,367
For Mixed Case and Upper Case fetch

Expand|Select|Wrap|Line Numbers
  1.  
  2. select * from emp where 
  3. LENGTH(REPLACE(TRANSLATE(name,'abcdefghijklmnopqrstuvwxyz',' '),' ','')) > 0
  4.  
  5.  
Nov 10 '08 #6

100+
P: 127
For Mixed Case and Upper Case fetch

Expand|Select|Wrap|Line Numbers
  1.  
  2. select * from emp where 
  3. LENGTH(REPLACE(TRANSLATE(name,'abcdefghijklmnopqrstuvwxyz',' '),' ','')) > 0
  4.  
  5.  
wouldn't it be easier to use

Expand|Select|Wrap|Line Numbers
  1. select * from emp
  2. where name != lower(name) --Only this line in case of mixed cases and upper cases
  3. and   name != upper(name) --add this line just to get mixed cases
  4.  
Nov 10 '08 #7

amitpatel66
Expert 100+
P: 2,367
Its always better not to use != (inequality) sign with strings

My solution that includes TRANSLATE,REPLACE can be easily rewritten using Regular Expressions in 10g. I am using 9i in my office so provided that solution
Nov 11 '08 #8

100+
P: 127
Well, in this case, the inequality is the lesser evil. The translate and replace will have to go through each string from the start to the end, even if there are no occurances of the occurance to replace or translate. Using a combination of translate and replace will make the query a lot slower. The inequality will stop with the comparison as soon as it finds the first difference.

Pilgrim.
Nov 11 '08 #9

amitpatel66
Expert 100+
P: 2,367
Its not becuase of performance that I am asking not to use inequality, its because it causes wrong results some times in real time scenarios
Nov 11 '08 #10

100+
P: 127
Ah ok, in some cases it can lead to wrong results indeed.

Pilgrim.
Nov 11 '08 #11

Saii
Expert 100+
P: 145
Which Oracle version are you using? In 10g you can use regular expressions
Nov 14 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.