How to select the First midde and last word in a phrase + SQL SERVER 2008

No Doubt "SQL SERVER" is a great utility that makes various operation more simple.
"Nothing is Impossible with SQL SERVER"

Now lets see how to select a last word from a phrase.

1. Selecting the first word in a pharse is simple as we trace for the first space in the phrase and get the charindex of the first space and we can make it done using the Left function.
like
SELECT LEFT('Be Always Happy',CHARINDEX(' ','Be Always Happy')-1)

Output:
Be

2. Now how to select the last word of the pharse ?
How we gonna trace for the last space in the phrase? let see
One method is like

SELECT PARSENAME(REPLACE('Be Always Happy', ' ', '.'), 1)

Output:
Happy

3.we can also select the second word form the right like

SELECT PARSENAME(REPLACE('Be Always Happy', ' ', '.'), 2)

Output:
Always

Replacing the 2 by 3 would select the third word from the phrase namely 'Be'
and go on as many word we have.

But this wont work at all the time say for example when there is dot at the end of the phrase like

SELECT PARSENAME(REPLACE('Be Always Happy.', ' ', '.'), 1)

Now the output is NULL for the above query.

So i found the method which can handle all possible situations. Lets see how?

Finding Last word of a phrase:

SELECT RIGHT('Be Always Happy.', CHARINDEX(' ', REVERSE('Be Always Happy.')) - 1)

Output:
Happy.
 
With the above query its done. There may be many way to achieve this but this is one simple idea.

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