count number of characters in nvarchar column

Question

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

User · Accepted Answer

You can find the number of characters using system function LEN  i e   SELECT LEN Column  FROM TABLE

User · Answer

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

User · Answer

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

User · Answer

Doesn t SELECT LEN column name  work

User · Answer

Use  SELECT length yourfield  FROM table

User · Answer

Use the LEN function      Returns the number of characters of the specified string expression  excluding trailing blanks

[sql] count number of characters in nvarchar column

Examples related to sql

Examples related to sql-server-2008

Examples related to datalength