Results 1 to 10 of 10

Thread: first 5 words

  1. #1
    Join Date
    Oct 2003
    Posts
    83

    Red face Unanswered: 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?

    Subhasish

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I imagine something like...

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Posts
    83
    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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    Code:
    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)

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah...I found the one I built...

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Sep 2003
    Posts
    522
    if you're just counting words, then you don't have to have a loop:

    Code:
    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

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Very cool....

    Just remove the delimiter and compare the string lendths...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  10. #10
    Join Date
    Sep 2003
    Posts
    522
    i was talking about avoiding the loop while counting words, not while returning the n number of words.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •