Thanks to everyone for the input. If I could normalize, or do more data entry validation (or verification for that matter) I would. The end users are insurance agents who represent many different insurance companies, Each company has a different policy number format and length. Some add dashes or spaces to make the number more readable to humans, but some don't. Add to that, the agency may manually enter the information into my database before the insurance carrier sends an update. To cap this off, the insurance carrier may alter the policy number when the policy renews, to indicate the renewal generation -- usually by adding characters at the end of the stub policy number. Matching is a nightmare, and will never be 100% accurate. My only hope is to match as well as I can, and when there is no match, get the user involved. Ugh.
I really appreciate the suggestions aimed at improving my methodology. I can always count on this group to help in so many ways. Unfortunately, I am constrained by circumstances beyond my control. My application must be able to print documents that present the policy numbers in the same format as the insurance company does. Forcing my own format on data entry would be unwise because I would not know how to reverse any alteration unless I added a "search" field (a waste of space). Anyway, here's the SQL I came up with, thanks to your kind instructions. It removes dashes and spaces, and the appended generational characters that also vary from one company to the next ( 2 in this case). Replace(Replace(SUBSTRING(a.PolNum from 1 for CHAR_LENGTH(a.PolNum)-2),'-',''),' ','') As PolNum
