One of the (many) things that I do is various ad-hoc statistical reports on data in SQL databases, so I’m forever churning out weird little SQL routines and cursors with the hope of extracting meaning from data.
Here’s a handy little number I put together just recently which counts the number of words in a field in a table. Words are counted individually (the delimiter inside a field is a space). So this routine will count the field “John” as one entry and the field “John Smith” as two separate entries, “John” and “Smith”. It then counts the results and displays a quick table with the most common words at the top and the number of occurrences.
Modify the cursor declaration to suit your own schema…
create table #tblCommonNames
(namepart varchar(100))
DECLARE @f varchar(50), @count int, @temp smallint
DECLARE cur CURSOR
FOR select distinct company_name
from tblCompany
set @count = 1
OPEN cur
FETCH NEXT FROM cur INTO @f
WHILE (@@fetch_status <> -1) -- and @count < 100)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
--find spaces
while (charindex(' ', @f) <> 0)
begin
set @temp = charindex(' ', @f)
insert into #tblCommonNames
(namepart)
values
(ltrim(rtrim(left (@f,@temp))))
set @f = ltrim(rtrim(substring(@f, @temp, 99)))
end
END
FETCH NEXT FROM cur INTO @f
SELECT @count = @count + 1
END
CLOSE cur
DEALLOCATE cur
delete
from #tblCommonNames
where ltrim(rtrim(namepart)) = ''
select namepart, count(namepart)
from #tblCommonNames
group by namepart
order by count(namepart) desc
drop table #tblCommonNames
go