Retrieve the maximum length of a VARCHAR column in SQL Server

Question

I want to find the longest VARCHAR in a specific column of a SQL Server table   Here s an example   ID   INT IDENTITY DESC   VARCHAR 5000   ID   Desc --- ----- 1    a 2    aaa 3    aa   What s the SQL to return 3  Since the longest value is 3 characters

User · Answer

SELECT MAX LEN Desc   as MaxLen FROM table

User · Answer

For Oracle  it is also LENGTH instead of LEN  SELECT MAX LENGTH Desc   FROM table name   Also  DESC is a reserved word   Although many reserved words will still work for column names in many circumstances it is bad practice to do so  and can cause issues in some circumstances   They are reserved for a reason   If the word Desc was just being used as an example  it should be noted that not everyone will realize that  but many will realize that it is a reserved word for Descending  Personally  I started off by using this  and then trying to figure out where the column name went because all I had were reserved words   It didn t take long to figure it out  but keep that in mind when deciding on what to substitute for your actual column name

User · Answer

for mysql its length not len  SELECT MAX LENGTH Desc   FROM table name

User · Answer

For sql server  SSMS   select MAX LEN ColumnName   from table name   This will returns number of characters    select MAX DATALENGTH ColumnName   from table name   This will returns number of bytes used required   IF some one use varchar then use DATALENGTH More details

User · Answer

Use the built-in functions for length and max on the description column   SELECT MAX LEN DESC   FROM table name    Note that if your table is very large  there can be performance issues

User · Answer

Gives the Max Count of record in table  select max len Description  from Table Name   Gives Record Having Greater Count   select Description from Table Name group by Description having max len Description    gt 27   Hope helps someone

User · Answer

SELECT TOP 1 column name  LEN column name  AS Lenght FROM table name ORDER BY LEN column name  DESC

User · Answer

Watch out   If there s spaces they will not be considered by the LEN method in T-SQL  Don t let this trick you and use  select max datalength Desc   from table name

User · Answer

For IBM Db2 its LENGTH  not LEN  SELECT MAX LENGTH Desc   FROM table name

User · Answer

Many times you want to identify the row that has that column with the longest length  especially if you are troubleshooting to find out why the length of a column on a row in a table is so much longer than any other row  for instance   This query will give you the option to list an identifier on the row in order to identify which row it is   select ID   description   len  description   as descriptionlength FROM  database1   dbo   table1  where len  description        select max len  description      FROM  database1   dbo   table1

[sql] Retrieve the maximum length of a VARCHAR column in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to string-length

Examples related to maxlength