SQL Server 2008 update one column with a value based on number words in other

Question

Columns Words, WordsCount

For each value of Words "x y z" update wordscount with 3 , "a b" with 2, "a b c d" with 4 and etc...

How can I do it with/without .Net program ?

Answer 1

You might be able to do this with a split function where you split on a space:

Create FUNCTION [dbo].[udf_Split] (@DelimitedList nvarchar(max), @Delimiter nvarchar(2) = ',')
RETURNS @SplitResults TABLE (Position int NOT NULL PRIMARY KEY, Value nvarchar(max))
AS
Begin
    Declare @DelimiterLength int
    Set @DelimiterLength = DataLength(@Delimiter) / 2

    If Left(@DelimitedList, @DelimiterLength) <> @Delimiter
        Set @DelimitedList = @Delimiter + @DelimitedList

    If Right(@DelimitedList, @DelimiterLength) <> @Delimiter
        Set @DelimitedList = @DelimitedList + @Delimiter

    Insert @SplitResults(Position, Value)
    Select CharIndex(@Delimiter, A.list, N.Value) + @DelimiterLength            
        , Substring (
                    A.List
                    , CharIndex(@Delimiter, A.list, N.Value) + @DelimiterLength         
                    , CharIndex(@Delimiter, A.list, N.Value + 1)                            
                        - ( CharIndex(@Delimiter, A.list, N.Value) + @DelimiterLength ) 
                    )
    From dbo.Numbers As N
        Cross Join (Select @DelimitedList As list) As A
    Where N.Value > 0
        And N.Value < LEN(A.list)
        And Substring(A.list, N.Value, @DelimiterLength) = @Delimiter
    Order By N.Value

    Return
End

Update Table
Set WordCount = (
                Select Count(*)
                From dbo.udf_Split(Table.WordlistCol, ' ')
                )

This version of a split function relies on the existence of a Numbers table which contains a sequential list of integer values.

Answer 2

If you can count on all words always being separated by one space:

UPDATE table SET WordCount = (LEN(Words) - LEN(REPLACE(Words, ' ', ''))) + 1

This breaks when there is nothing or NULL in Words, but you get the idea..