Function - Word count in a sentence

,

This function will give you a word count based on the number of spaces in a string.
Example:
SELECT WordCount
FROM [dbo].[udf_WordCount] ('It''s now or never I ain''t gonna live forever')
CREATE FUNCTION [dbo].[udf_WordCount] 
(

@str VARCHAR(8000) 

)
RETURNS TABLE AS RETURN

WITH Tally (n) AS
(
    SELECT TOP (LEN(@str)) ROW_NUMBER()  OVER (ORDER BY (SELECT NULL)) 
    FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)
)
, BreakChar as
(
SELECT  SUBSTRING(@str , n , 1) [Char] , N
FROM Tally

)
, Analize as 
(
SELECT * , lag([Char],1) OVER (ORDER BY N) PrevChar
FROM BreakChar
)

SELECT WordCount = COUNT(1) + 1 
FROM Analize
WHERE [Char] != PrevChar
AND PrevChar = ' '

Rate

4.33 (3)

Share

Share

Rate

4.33 (3)