first 5 words

From a column of datatype either text or varchar how can I get a definite number of words?
Suppose it got -'I am a BA from Calcutta.Currently I am in Delhi'
I want to retreive first 5 words which will provide me the output-
'I am a BA from'

How can I do that?

SubhasishI imagine something like...

CREATE FUNCTION FindFifthWord
(@.str varchar(1000))
RETURNS int
AS
BEGIN
DECLARE @.SpacesFound INT
DECLARE @.LastSpacePosition INT
SET @.SpacesFound = 0
SET @.LastSpacePosition = 0

WHILE (@.SpacesFound < 5)
BEGIN
IF (CHARINDEX(' ', @.str, @.LastSpacePosition + 1) = 0)
BREAK

ELSE
BEGIN
SET @.LastSpacePosition = CHARINDEX(' ', @.str, @.LastSpacePosition + 1)
SET @.SpacesFound = @.SpacesFound + 1
END

END

RETURN @.LastSpacePosition
END|||No sorry this will not help me.
I may have ' , ' or '.' or space in the words.
How can I remove those?
And I am not getting the required output.

Subhasish|||Well it has to be delimited by something...in my case in the UDF I used space as the delimeter...

There has to be something...otherwise how would you decide to split it up?

In a sentence, the natural delimeter would be a space.|||create function dbo.fn_N_NumberOfWords (
@.phrase nvarchar(4000),
@.HowMany int ) returns nvarchar(4000)
as begin
declare @.i int, @.pos int
declare @.tbl table (cnt int not null, pos int not null)

set @.phrase = replace(replace(@.phrase, '.', '. '), ' ', ' ')

select @.i = 1, @.pos = 1
while @.i <= @.HowMany begin
set @.pos = charindex(' ', @.phrase + ' ', @.pos+1)
insert @.tbl select @.i, @.pos-1
set @.i = @.i + 1
end
return (select substring(@.phrase, 1, pos) from @.tbl where cnt = @.HowMany)
end
go
select dbo.fn_N_NumberOfWords('I am a BA from Calcutta.Currently I am in Delhi', 4)|||Yeah...I found the one I built...

CREATE FUNCTION udf_WORDS
(@.str varchar(8000), @.Del char(1)=' ')
RETURNS int
AS

/* Mimic REXX function */
BEGIN
DECLARE @.Words INT, @.Pos INT, @.x Int
SELECT @.Words = 0, @.Pos = 1, @.x = -1

WHILE (@.x <> 0)
BEGIN
SET @.x = CHARINDEX(@.Del, @.str, @.Pos)
SET @.Pos = @.x + 1
SET @.Words = @.Words + 1
END

RETURN @.Words
END|||if you're just counting words, then you don't have to have a loop:

alter function dbo.fn_CountWords (
@.phrase nvarchar(4000),
@.delimiter nchar(1) ) returns int
as begin
set @.phrase = rtrim(ltrim(@.phrase))
if cast(reverse(@.phrase) as nchar(1)) != @.delimiter
set @.phrase = rtrim(ltrim(@.phrase)) + @.delimiter
return (
select (datalength(@.phrase) - datalength(replace(@.phrase, @.delimiter, '')))/2
)
end
go|||Very cool...

Just remove the delimiter and compare the string lendths...|||I like the concept. I think you need some logic to account for duplicate delimiters, such as two spaces between sentences.

subhasishray appears to want a function that returns the first five (or N?) words as a string. I think a loop cannot be avoided in solving this.

Create function NWords(@.CharString varchar(500), @.NWords int)
returns varchar(500)
as
begin
declare @.TempString varchar(500)
set @.CharString = ltrim(rtrim(@.CharString))
set @.TempString = @.CharString + ' '
while @.NWords > 0
begin
set @.TempString = ltrim(right(@.TempString, len(@.TempString) + 1 - CharIndex(' ', @.TempString)))
set @.NWords = @.NWords - 1
end
return left(@.CharString, len(@.CharString) - len(@.TempString))
end
GO

print dbo.NWords('This is my string, just for sample purposes. You are free to copy it if your like.', 5)
GO

Result:
This is my string, just

If he wants commas and periods stripped out, h have to use the REPLACE function.

blindman|||i was talking about avoiding the loop while counting words, not while returning the n number of words.