Link to home
Start Free TrialLog in
Avatar of Alex A
Alex A

asked on

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?
Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PortletPaul
PortletPaul
Flag of Australia image
DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.
http://msdn.microsoft.com/en-us/library/ms173486(v=sql.105)
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.
Avatar of Luan Jubica
Luan Jubica
Flag of Albania image
you can use ltrim, rtrim of you dont wont to count spaces

SELECT  MAX( LEN(ltrim(rtrim(ColumnName))) )  from yourTable
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image
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...