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 **
*********************************************************************************/