Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Max length of the contents in a Column

Posted on 2004-11-17
Medium Priority
Last Modified: 2012-05-05
Hello all,
I have a column of type varchar and of size 7000.Is there a function available to find the max length of contents in that column?

eg:table person{ssn,firstName,lastName}

I would like to find the max length of the column 'firstName' which is 7.

Question by:spankenstien
  • 2
LVL 26

Accepted Solution

Hilaire earned 80 total points
ID: 12604446
select max(len(firstName)) as [max length] from  person
LVL 26

Expert Comment

ID: 12604494
select max(datalength(firstName)) as [max length] from  person

The only difference in this case is that unlike the len function, datalenght does count trailing spaces.
You'd have to use datalength/2 for nvarchar datatype, since datalength calculates the size in bytes,
and nvarchar uses 2 bytes/character  (1 for varchar)
LVL 50

Expert Comment

ID: 12604495
if you want to know the length of the data in a column you always
need to use the DATALENGTH function

if you use Len(col) then that will ignore trailing blanks...

if the column is a Unicode data type Nvarchar etc.. then divide the result of DATALENGTH(col) by 2 to
get the number of characters long the data is.

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question