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?

score 237 · Accepted Answer · edited Oct 28 '18 at 00:48

237

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.

edited Oct 28 '18 at 00:48

Roy Scheffers

3,832
11
31
36

answered Mar 06 '12 at 21:35

aweis

5,350
4
30
46

Thank you, i had a brain fart and remembered the LEN function mid question but thought it might be good to have here anyway. Thanks mate! – Milo LaMar Mar 06 '12 at 21:38
18

Just a note, if you want to know size as opposed to number of characters, use `DATALENGTH` (since you may use `NVARCHAR` in some places, or someday). – Aaron Bertrand Mar 06 '12 at 21:48

score 61 · Answer 2 · edited Sep 06 '21 at 08:02

61

For MySQL, it's LENGTH, not LEN:

SELECT MAX(LENGTH(Desc)) FROM table_name

edited Sep 06 '21 at 08:02

Pang

9,564
146
81
122

answered Jul 04 '14 at 09:24

Abhishek Goel

18,785
11
87
65

2

Same 'LENGTH' function for postgresql as well. (not 'LEN') – dimuthu May 20 '20 at 05:46
1

How does this answer the question? – Yola Jun 06 '21 at 18:00

score 22 · Answer 3 · answered Nov 21 '14 at 14:06

22

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

answered Nov 21 '14 at 14:06

bevada

351
2
4

Good point. Actualy the varchars 'asd' and 'asd ' are treated the same by SQL Server (except in LIKE clause). For details check: https://support.microsoft.com/en-us/help/316626/inf-how-sql-server-compares-strings-with-trailing-spaces – yakya Apr 07 '17 at 07:34

score 10 · Answer 4 · answered Jan 28 '15 at 20:42

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.

score 9 · Answer 5 · answered Jun 22 '16 at 13:59

9

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)) >27

Hope helps someone.

answered Jun 22 '16 at 13:59

Shaiju T

6,201
20
104
196

score 7 · Answer 6 · edited Feb 21 '23 at 11:10

7

For SQL server (SSMS)

Option 1:

-- This returns number of characters
select MAX(LEN(ColumnName)) from table_name

Option 2:

-- This returns the number of bytes
select MAX(DATALENGTH(ColumnName)) from table_name

If you're using VARCHAR, use DATALENGTH. More details

edited Feb 21 '23 at 11:10

double-beep

5,031
17
33
41

answered Oct 18 '16 at 09:21

MSTdev

4,507
2
23
40

Alexandru-Codrin Panaite · Answer 7 · 2018-12-30T15:08:53.853

5

SELECT TOP 1 column_name, LEN(column_name) AS Lenght FROM table_name ORDER BY LEN(column_name) DESC

edited Dec 30 '18 at 15:08

answered Dec 28 '18 at 13:20

Alexandru-Codrin Panaite

574
6
9

4

While this code snippet may be the solution, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-‌code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – HMD Dec 28 '18 at 13:42
+1 for pointing out I used a SQL Server keyword DESC as a column name, which it allows for some reason LOL – Milo LaMar Dec 29 '18 at 20:55
Correct spelling can also be critical as some reading this may not realize 'Lenght' in the answer is actually a typo for 'length' (not that if should cause an error) – Jean-Claude DuBois Oct 28 '19 at 17:57

score 5 · Answer 8 · answered Jun 04 '19 at 19:41

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]

score 2 · Answer 9 · answered Mar 06 '12 at 21:37

2

SELECT MAX(LEN(Desc)) as MaxLen FROM table

answered Mar 06 '12 at 21:37

Milo LaMar

2,146
2
14
25

score 1 · Answer 10 · edited Oct 26 '21 at 05:20

1

select * from table name from where length( column name) =(select MAX(Length(column  name) from table name);

I am using subquery its 100 % work try this.

edited Oct 26 '21 at 05:20

Suraj Rao

29,388
11
94
103

answered Oct 26 '21 at 05:19

ankush sonone

21
1
3

score -1 · Answer 11 · answered Aug 31 '20 at 15:16

-1

For IBM Db2 its LENGTH, not LEN:

SELECT MAX(LENGTH(Desc)) FROM table_name;

answered Aug 31 '20 at 15:16

Shashank

709
8
16

Retrieve the maximum length of a VARCHAR column in SQL Server

11 Answers11

Linked

Related