How the change the First letter of each word in a string to caps + SQL SERVER 2008

The Following is the way to achieve this:

DECLARE @Name VarChar(25), @SpaceIndex TinyInt

SET @Name = 'be happy' // is the input string


-- Determines whether the string has more than one word:


SET @SpaceIndex = CHARINDEX(' ', @Name)

IF @SpaceIndex <> 0


-- Space: Capitalize first & substring


SELECT UPPER(LEFT(@Name, 1))

+ LOWER(SUBSTRING(@Name, 2, @SpaceIndex - 1))

+ UPPER(SUBSTRING(@Name, @SpaceIndex + 1, 1))

+ LOWER(SUBSTRING(@Name, @SpaceIndex + 2, LEN(@Name)))


ELSE

-- No space: Cap only first char.


SELECT UPPER(LEFT(@Name, 1))

+ LOWER(SUBSTRING(@Name, 2, LEN(@Name)))

OUTPUT:
Be Happy