Lower-case or invalid characters in a customer database

[Madawc]

Where I work, the system runs on a mainframe with various PC ‘front-ends’. Crystal is used for head-office analysis of the data, with the Crystal database reflecting the position as it was yesterday.

Customer and account details are supposed to be typed in as UPPER CASE, with just the standard ‘special characters’. I was asked to write a program that would spot invalid characters among the hundreds of thousands of customer details. I did this using the following Boolean, which I then tested for in Record Selection:

Instr({Customer.Surname}, "`") > 0 or
Instr({Customer.Surname}, "|") > 0 or
Instr({Customer.Surname}, "\") > 0

The details could then be manually corrected using the mainframe system (which is highly secure). This was fine, and I was asked to extend it to cases where lower-case names had been used.

I could have repeated the same test for each lower-case letter, but that seemed long winded. I decided that UpperCase("Mr Smith") would return "MR SMITH", a difference which could be spotted even in record selection. Specifically, I tried:

{Customer.Surname} <> UpperCase({Customer.Surname})

Bafflingly, this found nothing. Luckily a colleague spotted the problem: Crystal was cleverly ignoring the difference in case, which would be just what you’d want in most cases. In this instance, the answer was:

StrCmp ({Customer.Surname}, UpperCase({Customer.Surname}), 0) <> 0

This is apparently just like a Visual Basic and will be obvious if you already know that language, but not to me. The final ‘0’ specifies that the comparison is to be case-sensitive, with ‘1’ indicating case-insensitive. ‘0’ is the default, at least for Crystal 10, but defaults sometimes change. I prefer my code to be as explicit as it reasonably can be.

I could find nothing similar from a Search for ‘lower case’, so I thought the solution would be worth posting.


Madawc Williams (East Anglia, UK)