Finding Uppercase Values

[aziz rasul]

How can I create a query that will return all records that contains an uppercase letter anywhere (other than that at the beginning) in a name field of a table?

I know how to resolve the problem using VBA, but a colleague of mine is using SQL only on his system (Oracle Discoverer?) and does not want to use any VB. I don't think it's possible to do what he requires but I thought I would place the problem on the forum.

What I've been able to do so far is to use InStr(1,[Name],Chr(65),0), InStr(1,[Name],Chr(66),0) etc. in separate fields and place an Or criteria of >0. However I would have to create 26 separate fields and I'm not sure if that's possible. The SQL statement I get is for A-C:

SELECT tblSFF.Name, InStr(2,[Name],Chr(65),0) AS A, InStr(2,[Name],Chr(66),0) AS B, InStr(2,[Name],Chr(67),0) AS C
FROM tblSFF
WHERE (((InStr(2,[Name],Chr(65),0))>0)) OR (((InStr(2,[Name],Chr(66),0))>0)) OR (((InStr(2,[Name],Chr(67),0))>0));

Is there an easier of solving the problem?

 

[Mile-O]

Can I ask, firstly, why you'd need to know if there were upper case characters in a field?

 

[aziz rasul]

If there are uppercase values in a name field it is regarded as an error because the names have to appear in telephone directories. Any uppercase values need to be rectified before publication.

 

[Mile-O]

Then why not perform a string conversion to ProperCase across all records? Those that are faulty will be fixed and those that are not will just be reformatted but won't change.

 

[aziz rasul]

Silly me. I didn't think of that. I'll try that and check with my colleague to see whether resolves the issue.

 

[aziz rasul]

My colleague tells me that he is only required to report the errors and not to rectify them.