OK in another shameless adaptation of Tally examples, I'll attempt to get Jeff Moden some more recognition.

Here is a propercase function based on the Tally table. here I'm checking for space, accents like O'Brian and D`Angelo, but not fiddling with Mac or Mc; (re: what happens when you propercase macaroni?) feel free to adapt it:

the problem here is if you propercase a phrase like "I like the dog's ears", it's not right, because of the single quote:

"i Like The Dog'S Ears",

I suggest changing the IN statement to only include the single space., or make a new ProperCaseAName function to use separately for firstname/lastname.

[font="Courier New"]--===== Create and populate the Tally table on the fly

SELECT TOP 11000 --equates to more than 30 years of dates        

IDENTITY(INT,1,1) AS N  

INTO dbo.Tally  

FROM MASTER.dbo.SysColumns sc1,        MASTER.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance  

ALTER TABLE dbo.Tally    

ADD CONSTRAINT PK_Tally_N         PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

GO

ALTER FUNCTION ProperCase(@OriginalText VARCHAR(8000))

RETURNS VARCHAR(8000)  

BEGIN

DECLARE @CleanedText VARCHAR(8000)

SELECT @CleanedText = ISNULL(@CleanedText,'') +  

     --first char is always capitalized?

CASE WHEN Tally.N = 1 THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))

     WHEN SUBSTRING(@OriginalText,Tally.N -1,1) IN( ' ','''','`')  THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))

     ELSE LOWER(SUBSTRING(@OriginalText,Tally.N,1))

END

FROM dbo.Tally           WHERE Tally.N <= LEN(@OriginalText)            

RETURN @CleanedText

END

SELECT dbo.ProperCase('WHAT THE HECK IS GOIN ON AROUND HERE MR D''ANGELO?;')

[/font]