>>SELECT COUNT(*) as keycount, keyfield FROM table1 GROUP BY upper(keyfield) ORDER BY upper(keyfield) HAVING keycount >1
>>
>>Also, if you have a large table, make sure that you have and index on upper(keyfield)
>>Hope this helps //:^)
>>
>
>The above is exactly what I'm looking for, thanks! What really confused me though is that my MS SQL Server will find a duplicate record if I issue the following:
>
>SELECT licnum FROM drivauth GROUP BY licnum HAVING COUNT(*)>1
>
>And the licnum in one record is "s422" and the other record is "S422"
>
>Jeff
It also helps (if you have the usual standard of data entry) to set the case of your data at input. e.g. we have a setup where data is forced by the field validation rules of the table to be upper, lower, or proper case as required.
Where ever possible validate data on entry -- that way you save a lot of hassles trying to find "s422" when you want "S422" etc. <g>
Incidently, even though VFP is not case-sensitive in its syntax, I find it easier to read when I use uppercase for commands and lowercase for variable names... or vice versa <g>
Actually the convention used in the manual u/c commands and l/c for variables is less readable than the other way round.... it makes the code SHOUT AT YOU!!
Cheers,
Jen
A bipolar theory does not neatly describe a continuum.
Before millenium: chop wood, draw water. After millenium: chop wood, draw water.