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]
Lowell