Technical Article

Number of words within a phrase function

,

Most word count functions/procedures are based on some form of looping methods. If the table is large or there is a need to count the words in a number of columns, this can become quite an exercise.
This function, based on a mathematical model, will work much faster and more efficient in counting the words within a text phrase of a column. This function assumes that a space or spaces delimits words within a phrase.

CREATE FUNCTION fn_count_words
    (@phrase varchar(100) = NULL)
RETURNS int

AS
Begin   
  declare @cnt_one int,@cnt_two int
  set @phrase=replace(replace(replace(@phrase,'  ','  
       $'),'$ ',''),' $',' ')
  set @cnt_one=isnull(nullif(len(ltrim(rtrim 
      (@phrase))),''),-1)
  set @cnt_two=len(ltrim(rtrim(replace(@phrase,' ',''))))-1

  return @cnt_one - @cnt_two
end

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating