declare @x table ( words varchar(50) not null )
insert  @x
select 'This is the first line'  union all
select 'Second line'	            union all
select 'Word'                     union all
select 'This is the fourth line' 


select	case when charindex(' ', words, charindex(' ', words) + 1) = 0
             then words
	      else left(words, charindex(' ', words, charindex(' ', words) + 1))
	 end
from  @x
go

--------------------------------------------------
This is 
Second line
Word
This is 

(4 row(s) affected)

CREATE FUNCTION udf_GetNumberOfWords (
  @stringToSplit varchar(8000), 
  @numberOfWords int
)

RETURNS varchar(8000) AS 

BEGIN 

DECLARE @currentword varchar(8000)
DECLARE @returnstring varchar(8000)
DECLARE @wordcount int
SET @wordcount = 0
SET @returnstring = ''
SET @currentword = ''
SET @stringToSplit = ltrim(rtrim(@stringToSplit))
Declare @index int

WHILE @wordcount   0
  BEGIN
    Select @index = CHARINDEX(' ', @stringToSplit)
    if @index = 0 
      BEGIN
        SELECT @currentword = ltrim(rtrim(@stringToSplit))
        SELECT @wordcount = @numberOfWords 
      END
    else 
      BEGIN
        IF (len(@stringToSplit) - @index > 0) BEGIN
        SELECT @currentword = ltrim(rtrim(LEFT(@stringToSplit, @index-1)))--the new shortened string
        SELECT @stringToSplit = RIGHT(@stringToSplit,LEN(@stringToSplit) - @index) -- the rest
      END
    END
  SELECT @returnstring = @returnstring + ' ' + @currentword
  SELECT @wordcount = @wordcount + 1 
END

SET @returnstring = LTRIM(@returnstring)
RETURN @returnstring

END

select dbo.udf_GetNumberOfWords(mycolumn,2)
from mytable