How can I find lowercase in a particular field? 3

[loveyoursite]

CRV10. I have a field {EMPLOYEE.EMAIL_ADDRESS} that should be all uppercase but unfortunately many are not. Does anyone know how I can search this field returning only those that are either all lowercase or contain any lowercase letters? Thanks!

 

[lbass]

Why not just enclose the field in ucase() ? Anyway, to check for lower case, first you must go to file->report options and uncheck "database server is case-insensitive" and then create a formula like this:

whileprintingrecords;
local numbervar i := i + 1;
stringvar display := "";
numbervar cnt := 0;

for i := 1 to len({table.field}) do(
if {table.field} = lcase({table.field}) then (
cnt := cnt + 1 ));
if cnt > 0 then
display := "Contains lower case" else
display := "OK";
display

-LB

 

[Turkbear]

Hi,
If you have access to the data directly ( you fail to mention what database is involved), use its tools to convert all the records to UpperCase..

In Oracle, for instance, it would be SqlPlus like:
Update EMPLOYEE set EMAIL_ADDRESS = UPPER(EMAIL_ADDRESS);





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

The other option would be to use a SQL expression:

ucase(table.`field`)

-LB

 

[loveyoursite]

Thanks for your solution LB. I tried it and every one came back with "contains lower case". A correct email address in our database looks like this: JANE.DOE@COMPANYNAME.COM

An incorrect email may be jane.doe@companyname.com or Jane.Doe@CompanyName.com

I must have done something wrong. I did uncheck the case-insensitive as you instructed.

By the way, this is an Oracle database, and I do not have access to its tools.

Thanks!

 

[Turkbear]

Hi,
Ask your DBA ( or whoever does have access to/manages the data) to correct the mistakes and to set up a method to insure future inserts are in all Upper Case...

When report developers create work-arounds for bad data, it allows those responsible for designing a faulty database app to avoid responsibility...


Until then, using the Sql expression in your report will at least display it correctly..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Did you uncheck the case-insensitive in the report options (not Options)? In some cases a change in Options dosen't take hold until you open a new report.

I tested this solution, and it worked, so you might want to copy your formula into the thread.

-LB

 

[loveyoursite]

Yes, I unchecked the case-insensitive in the report options and not in options. I closed the report and reopened it. The only one's that came back with OK were those with a blank email field. All others, both uppercase and lowercase came back with "contains lower case". We can ignore any that are blank. Here is the formula I used:

whileprintingrecords;
local numbervar i := i + 1;
stringvar display := "";
numbervar cnt := 0;

for i := 1 to len({EMPLOYEE.EMAIL_ADDRESS}) do(
if {EMPLOYEE.EMAIL_ADDRESS} = lcase({EMPLOYEE.EMAIL_ADDRESS}) then (
cnt := cnt + 1 ));
if cnt > 0 then
display := "Contains lower case" else
display := "OK";
display

 

[lbass]

Okay, the issue is that the symbols "." and "@" are being interpreted as lower case, as would any numbers. So change the formula to:

whileprintingrecords;
local numbervar i := i + 1;
stringvar display := "";
numbervar cnt := 0;
stringvar x := "";
numbervar j;

for i := 1 to len({EMPLOYEE.EMAIL_ADDRESS}) do(
if isnumeric({EMPLOYEE.EMAIL_ADDRESS}) or
{EMPLOYEE.EMAIL_ADDRESS} in ["@","."] then
x := x else
x := x + {EMPLOYEE.EMAIL_ADDRESS});
for j := 1 to len(x) do(
if x[j] = lcase(x[j]) then
cnt := cnt + 1);
if cnt > 0 then
display := "Contains lower case" else
display := "OK";
display

-LB

 

[loveyoursite]

Perfect! Thank you so much!