Just a few days ago there was a thread on one of Rocket Software's community forums about users entering car license plates and searching the database for them. Their database has license plates like this list: QAA.6814, QAA-6814, QAA 6814, QAA/6814, or QAA6814. So, the proposed solutions were to create an index that cleans them up using "MCAN in the OCONV() function only leaves alpha and numeric character, eliminating all others." My best guess is this shop is a telnet shop using D3, a Rocket Software database.
You should be asking yourself why am I laughing when I should be crying? Using telnet you and D3 BASIC code which does not support regular expressions (i.e. regex) put you in a bind. You can use an OCONV to clean up the data as the user enters it or not and get this mess as described above.
The first thing any programmer worth their salary should be asking themselves, why have you not cleaned up the data? After you have done that fix the data entry program to stop letting the garbage in. Fixing this index for the user inquiry is like putting a tiny bandage on a head wound that keeps bleeding it takes a lot more to stop this kind of thing.
The laughing part is from realizing that I stopped building telnet applications some 20-plus years ago and stopped getting junk in the Universe/Unidata/D3 databases. With HTML5 you can add a regular expression to a pattern option. This means that garbage data would not get into your database because you stopped it at the time of input. When you index the field you would not need to clean it up just create an index on the data field.
Since I moved away from telnet and cleaned up our databases for our clients and ourselves, I cannot think of a time when we have had to clean up anything. Before there was replication, I wrote a routine to capture the changed data with a before image and an after image. That way if a user deleted a record or I needed to find the errand user that screwed up the data. Our XLr8Audit system had the goods to restore the deleted record of informing the boss and the user of the problem that was created by them which could be backed out as well.
Our job as programmers is not to let this stuff in the first place. If you do then your battle will be a lifetime of work to keep up with users that you let them enter anything. A former staffer that did our QA always said to us programmers that if you allow them to enter GIGO the problems are with you not them.
Someday, you have to stop programming in telnet and embrace new technology. Those companies who do not do this will have their software replaced and maybe their whole department. A former Universe client had roughly 5 programmers that wrote the entire system that ran a county in Colorado. The entire staff was replaced by 40 programmers writing in Java and other languages using Microsoft SQL Server, not Universe/Unidata/D3 databases.
