Here is one I have wished I had before. I was bored... so I thought I would go ahead and write it.
Code:
ALTER FUNCTION initCaps (@sInitCaps varchar(8000))
/*********************************************************************************
**	Written by Kyle Freeman, 12/23/2002					**
**	Function:  Returns a string with the intial letter of all words		**
**		capitalized, and the rest of the word in lower case.		**
**		Unfortunately, it does not currently preserve spacing in the	**
**		string it is run on, so running it on a string where spacing	**
**		needs to be preserved is not recommended.  This function	**
**		is very similar to the initcap() function in Oracle.		**
*********************************************************************************/
RETURNS varchar(8000)
AS
BEGIN
/*********************************************************************************
** 	Start off by declaring variables					**
*********************************************************************************/
DECLARE @nNumSpaces int,
	@sParseString varchar(8000),
	@sParsedString varchar(8000)

/*********************************************************************************
**	Start this off by replacing all spaces in the string 			**
** 	and getting the string length, and subtract that     			**
** 	from the original, add one to it so it will parse    			**
** 	correctly.					       			**
*********************************************************************************/
SELECT @nNumSpaces = (LEN(@sInitCaps) - LEN(REPLACE(@sInitCaps, ' ', '')))+1

/*********************************************************************************
**	Replace all the spaces with a '%' so formatting stays correct.		**
*********************************************************************************/
SELECT @sInitCaps = REPLACE(@sInitCaps, ' ', '%')

/*********************************************************************************
**	Loop through the statement, picking the string apart with SUBSTRING	**
*********************************************************************************/
WHILE @nNumSpaces > 0
BEGIN
	/*************************************************************************
	**	Check to see if there are any '%' left with charindex.		**
	**	If so... continue parsing the string. 				**
	*************************************************************************/
	IF (CHARINDEX('%', @sInitCaps) <> 0)
	BEGIN
		SELECT @sParseString = SUBSTRING(@sInitCaps, 1, CHARINDEX('%',@sInitCaps))
		SELECT @sInitCaps = SUBSTRING(@sInitCaps, (CHARINDEX('%', @sInitCaps)+1), LEN(@sInitCaps))
		SELECT @nNumSpaces = @nNumSpaces - 1

	END
	ELSE
	BEGIN
		/*****************************************************************
		**	No more spaces left.					**
		*****************************************************************/
		SELECT @nNumSpaces = @nNumSpaces - 1
		SELECT @sParseString = @sInitCaps
	END
	/*************************************************************************
	**	This is where we put the string back together.			**
	**	We take one parameter from the string, process it in the case   **
	**	statement, then change the '%' back to ' '  Fortunately, 	**
	**	the '%' character doesnt have an UPPER so it is processed on	**
	**	it's own fine when it would end up in the parsed string by	**
	**	itself as one character, which would occur when doublespaced.	**
	*************************************************************************/

	SELECT @sParsedString = REPLACE(ISNULL(@sParsedString, '') + CASE WHEN LEN(@sParseString) = 1 THEN UPPER(@sParseString)
								ELSE UPPER(SUBSTRING(@sParseString, 1, 1)) + LOWER(SUBSTRING(@sParseString, 2, LEN(@sParseString)))
								END, '%', ' ')
END

RETURN(@sParsedString)
END

/*********************************************************************************
**	Usage									**
**	select master.dbo.initCaps('ForMaTted  TEXT.')				**
**	Returns									**
**	'Formatted  Text'							**
**										**
**	Using against a table...						**
**	select master.dbo.initCaps(ProductName) from Northwind.dbo.products	**
*********************************************************************************/