Query to fetch name with mixed case

[suvitha]

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.

[debasisdas]

Can you post the query that you are working on.

[suvitha]

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.

[suvitha]

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

ERROR at line 1:ORA-00920: invalid relational operator

[suvitha]

[IMG]Hello[/IMG]

Found the query:

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


Hope this will help others

[amitpatel66]

For Mixed Case and Upper Case fetch

 
select * from emp where 
LENGTH(REPLACE(TRANSLATE(name,'abcdefghijklmnopqrstuvwxyz',' '),' ','')) > 0
 
 

[Pilgrim333]

For Mixed Case and Upper Case fetch

 
select * from emp where 
LENGTH(REPLACE(TRANSLATE(name,'abcdefghijklmnopqrstuvwxyz',' '),' ','')) > 0
 
 

wouldn't it be easier to use

select * from emp
where name != lower(name) --Only this line in case of mixed cases and upper cases
and   name != upper(name) --add this line just to get mixed cases
 

[amitpatel66]

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

[Pilgrim333]

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.

[amitpatel66]

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

[Pilgrim333]

Ah ok, in some cases it can lead to wrong results indeed.

Pilgrim.

[Saii]

Which Oracle version are you using? In 10g you can use regular expressions