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)
- 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.