kyle (1/27/2009)
We basically just want all the names in the field to be proper cased as you said most people are used to seeing Macdonald.After further thought, we could just run two separate queries if we can determine how many spaces are in a field using the WHERE clause.
Does anyone have any idea how to count specific characters (spaces) in a field?
you can count the spaces in the field using
SELECT LEN(FIRSTNAME) - LEN(REPLACE(FIRSTNAME,' ',''))
I did something similar recenlty where we had to sort out the casing on some names. It is difficult to do this with code alone. I ended up creeating a look-up table that had a lot of the common names we had and used this to do the conversion. We did not trust the accuracy of using UPPER statements alone.
Also i would have thought most people are used to seeing 'MacDonald' rather than 'Macdonald'