The code below returns a data set of "word" and 'Count' from the words used in the table JobNo and the Column BuyerNotes. Very useful for building a tag cloud, or a filter to group sets of records.


Code:
WITH E1(N) AS (    SELECT 1 FROM (VALUES
        (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ) t(N)
),
E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b)
SELECT TOP 100
    x.Item,
    COUNT(*)
FROM JobNo p
CROSS APPLY (
    SELECT 
        ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = LTRIM(RTRIM(SUBSTRING(p.BuyerNote, l.N1, l.L1)))
        FROM (
            SELECT s.N1,
                L1 = ISNULL(NULLIF(CHARINDEX(' ',p.BuyerNote,s.N1),0)-s.N1,4000)
            FROM(
                SELECT 1 UNION ALL
                SELECT t.N+1 
                FROM(
                    SELECT TOP (ISNULL(DATALENGTH(p.BuyerNote)/2,0))
                        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
                    FROM E4
                ) t(N)
                WHERE SUBSTRING(p.BuyerNote ,t.N,1) = ' '
            ) s(N1)
        ) l(N1, L1)
) x
WHERE x.item <> ''
GROUP BY x.Item
ORDER BY COUNT(*) DESC
written by Jeff Moden and improved by members of the SQL Server Central community.