0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

CREATE TABLE #words(word varchar(max), count int);


DECLARE c CURSOR FOR SELECT TOP 5000 Posts.Title FROM Posts, PostHistory
                     WHERE Posts.Id = PostHistory.PostId
                     AND PostHistory.PostHistoryTypeId = 10
                     AND PostHistory.Comment = '3';
OPEN c

DECLARE @title varchar(max), @word varchar(max), @pos int;
FETCH NEXT FROM c INTO @title
WHILE @@fetch_status = 0 BEGIN
    WHILE RTRIM(@title) != '' BEGIN
        SET @pos = CHARINDEX(' ', @title);
        IF @pos = 0 BREAK -- Seriously, T-SQL?
        SET @word = LOWER(SUBSTRING(@title, 0, @pos));
        UPDATE #words SET count = count + 1 WHERE word = @word;
        IF @@ROWCOUNT = 0
            INSERT INTO #words VALUES(@word, 1);
            SET @title = SUBSTRING(@title, @pos + 1, 1000000); -- Not sure why DATALENGTH(@title) - (@pos + 1) doesn't work
    END
    
    FETCH NEXT FROM c INTO @title
END

CLOSE c
DEALLOCATE c
SELECT TOP 10 * FROM #words where len(word) >3 ORDER BY count DESC;

fork query

edited apr 27 12 jon.doe6850

created apr 27 12 Michael Mrozek