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

Pneumatograph

TEXT is deprecated and shouldn't be used. – Duhon

"but this only works for text" how so? Everything that goes in an nvarchar column 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. – Coulometer

Answer 1

You can find the number of characters using system function LEN. i.e.

SELECT LEN(Column) FROM TABLE

Linell

'LEN' will give you the count till 4000 only. So, how you will get the count of characters greater than 4000 in length? – Gaiety

I didn't get you... I guess it counts string value more than 4000 length. Please confirm. – Linell

hi @TechDo, I have an HTML content and using your solution i have tried to get length of the content which is more than 4000 charcters but dont know why it returns lenght as 4000 only!So, i said..!any idea about this? – Gaiety

@SHEKHARSHETE There is no character limit: msdn.microsoft.com/en-us/library/ms190329%28v=sql.120%29.aspx – Redness

Yes! i agree! but by SQLserver allow me not more than 4000 charcters! do we need to do any setting in Management studio? – Gaiety

I tried in MySQL as well, and it says #1305 - FUNCTION sampleDB.len does not exist when I did so. And using length instead of len worked. – Calculation

FYI, in postgres, this is SELECT LENGTH(column) FROM table. – Diggins

would this also consider whitespace if it was included in the field? – Zinck

Answer 2

Use

SELECT length(yourfield) FROM table;

Sanders

in DataBricks, length works len does not work – Cytolysis

Answer 3

Use the LEN function:

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

Duhon

Answer 4

Doesn't SELECT LEN(column_name) work?

Kidron

Answer 5

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

Mingy

Answer 6

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.

Legitimize