T-SQL: max lenth of text in nvarchar(4000)

Hi, how to determine maximum lenth of existing text in SQL Server table nvarchar(4000) column?
Who is Participating?
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
SELECT  MAX( DATALENGTH(ColumnName) )  from yourTable
SELECT  MAX( LEN(ColumnName) )  from yourTable
DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.
datalength() is the number of bytes involved (including trailing white space if any)

Whereas len() is the number of characters (but one character can be multi-byte) and it  excludes trailing blanks.
Luan JubicaProject ManagerCommented:
you can use ltrim, rtrim of you dont wont to count spaces

SELECT  MAX( LEN(ltrim(rtrim(ColumnName))) )  from yourTable
Patrick MatthewsCommented:
luani wrote:

you can use ltrim, rtrim of you dont wont to count spaces

That would only strip out leading and/or trailing spaces, and will leave intact any internal spaces.

That said, if the concern is for trailing spaces, then as PortletPaul already indicates, LEN() already leaves those out...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.