[sql] count number of characters in nvarchar column

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

Doesn't SELECT LEN(column_name) work?

Use

SELECT length(yourfield) FROM table;

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.

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.

Source

Use the LEN function:

Returns the number of characters of the specified string expression, excluding trailing blanks.