Jeremy Coenen

Get word count from SQL Server table

For one of my projects I needed to go into the database and get a rough estimate of the number of words that needed to be translated from various tables in our database.

I looked around to see if anyone had come up with some t-sql to do this, but did not find much in my initial search so I ended up creating this rudimentary stored procedure.

 
CREATE PROCEDURE GetWordCount (
    @tableName NVARCHAR(256),
    @columnName NVARCHAR(256)
)
AS
DECLARE @SQL NVARCHAR (4000)
 
SELECT @SQL = 'SELECT SUM( (1 + DATALENGTH(' +@columnName +') -
 DATALENGTH(REPLACE(CAST('+ @columnName + '
 AS NVARCHAR(MAX)), '' '', '''')))) AS WordCount 
FROM ' + @tablename
 
EXEC SP_EXECUTESQL @SQL
 

I'm sure there are flaws in the design (basically counting spaces), but it gets me a close enough estimate.

Note: I ended up using DataLength() over LEN() to be able to hand text/ntext fields and also casted the column as NVARCHAR(MAX) for the very same reason.