17 December 2007 17:15
tonyrogerson
Counting the number of words in a string
Are you trying to count how many words there are in a string? Well, first I looked at the LEN and REPLACE ( ... ', ' ', '' ) method but you soon realise it doesn't work - you start counting double spaces as actual words.
Here is a function that gets round the problem; it also excludes non word stuff like a question mark.
create function fn_how_many_words(
@source varchar(max) )
returns int
as
begin
declare @start int
-- break any multiple spaces down
while 1=1
begin
set @start = len( replace( @source, ' ', ' ' ) )
set @source = replace( @source, ' ', ' ' )
if @start = len( replace( @source, ' ', ' ' ) )
break
end
-- get rid of any non letters and numbers
set @start = 1
while @start <= 255
begin
if @start not between ascii( 'a' ) and ascii( 'z' )
and @start not between ascii( 'A' ) and ascii( 'Z' )
and @start not between ascii( '0' ) and ascii( '9' )
and @start <> ascii( ' ' )
set @source = replace( @source, char( @start ), '' )
set @start = @start + 1
end
return( len( @source ) - len( replace( @source, ' ', '' ) ) + 1 )
end
Filed under: SQL Server