Max length of the contents in a Column

Posted on 2004-11-17
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
    LVL 26

    Accepted Solution

    select max(len(firstName)) as [max length] from  person
    LVL 26

    Expert Comment

    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

    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

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now