Does anyone know a good way to count characters in a text (nvarchar) column in Sql Server? The values there can be text, symbols and/or numbers.
So far I used sum(datalength(column))/2 but this only works for text. (it's a method based on datalength and this can vary from a type to another).
You can find the number of characters using system function LEN. i.e.
SELECT LEN(Column) FROM TABLE 
Doesn't SELECT LEN(column_name) work?
text doesn't work with len function.
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.
I had a similar problem recently, and here's what I did:
SELECT columnname as 'Original_Value', LEN(LTRIM(columnname)) as 'Orig_Val_Char_Count', N'['+columnname+']' as 'UnicodeStr_Value', LEN(N'['+columnname+']')-2 as 'True_Char_Count' FROM mytable The first two columns look at the original value and count the characters (minus leading/trailing spaces).
I needed to compare that with the true count of characters, which is why I used the second LEN function. It sets the column value to a string, forces that string to Unicode, and then counts the characters.
By using the brackets, you ensure that any leading or trailing spaces are also counted as characters; of course, you don't want to count the brackets themselves, so you subtract 2 at the end.
TEXTis deprecated and shouldn't be used.nvarcharcolumn has to be, at the end of the day, composed of sequences of unicode characters, stored as UTF-16, and your method counts how many of those characters there are.