Nov 12, 2009

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.
SELECT LEFT('Be Always Happy',CHARINDEX(' ','Be Always Happy')-1)


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)


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

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


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)

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

No comments: