• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 740
  • Last Modified:

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.
0
quasar_ee
Asked:
quasar_ee
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT  MAX( DATALENGTH(ColumnName) )  from yourTable
SELECT  MAX( LEN(ColumnName) )  from yourTable
0
 
PortletPaulfreelancerCommented:
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.
0
 
Luan JubicaProject ManagerCommented:
you can use ltrim, rtrim of you dont wont to count spaces

SELECT  MAX( LEN(ltrim(rtrim(ColumnName))) )  from yourTable
0
 
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...
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now