I know this is an older post but, if you're interested in performance, here's a new method I ran across. Yeah... I know. The RBAR method is actually faster than the Tally Table method because THIS RBAR method is all in memory. As good as it is, even a cached Tally Table can't quite keep up.

Almost forgot... here's the code I ran across. Kudos to George Mastros for the original concept.

CREATE FUNCTION dbo.InitialCap(@String VARCHAR(8000))

/***************************************************************************************************

Purpose:

Capitalize any lower case alpha character which follows any non alpha character or single quote.

Revision History:

Rev 00 - 24 Feb 2010 - George Mastros - Initial concept

http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-proper-case-function

Rev 01 - 25 Sep 2010 - Jeff Moden

- Redaction for personal use and added documentation.

- Slight speed enhancement by adding additional COLLATE clauses that shouldn't have mattered

- and the reduction of multiple SET statements to just 2 SELECT statements.

- Add no-cap single-quote by single-quote to the filter.

***************************************************************************************************/

RETURNS VARCHAR(8000)

AS

BEGIN

----------------------------------------------------------------------------------------------------

DECLARE @Position INT

;

--===== Update the first character no matter what and then find the next postion that we

-- need to update. The collation here is essential to making this so simple.

-- A-z is equivalent to the slower A-Z

SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,

@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

;

--===== Do the same thing over and over until we run out of places to capitalize.

-- Note the reason for the speed here is that ONLY places that need capitalization

-- are even considered for @Position using the speed of PATINDEX.

WHILE @Position > 0

SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,

@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

;

----------------------------------------------------------------------------------------------------

RETURN @String;

END ;