469,343 Members | 5,350 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,343 developers. It's quick & easy.

Query to fetch name with mixed case

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
11 8262
debasisdas
8,127 Expert 4TB
Can you post the query that you are working on.
Nov 6 '08 #2
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
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
[IMG]Hello[/IMG]

Found the query:

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


Hope this will help others
Nov 6 '08 #5
amitpatel66
2,367 Expert 2GB
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
Pilgrim333
127 100+
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
2,367 Expert 2GB
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
Pilgrim333
127 100+
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
2,367 Expert 2GB
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
Pilgrim333
127 100+
Ah ok, in some cases it can lead to wrong results indeed.

Pilgrim.
Nov 11 '08 #11
Saii
145 Expert 100+
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.

Similar topics

10 posts views Thread by Wonderinguy | last post: by
7 posts views Thread by Jim Carlock | last post: by
4 posts views Thread by natG | last post: by
9 posts views Thread by s0suk3 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.