SQL Server - Get longest value in a column

The other day at work I was trying to find out what are the distinct values in one of the columns in a table within my database.

As you most probably know this can be done by using the DISTINCT keyword in SQL. So a quick example would be the following:

SELECT DISTINCT <COLUMN_NAME>
FROM <TABLE_NAME>

But what I wanted to find out was which value was the longest. Using the ORDER BY clause would sort out the resulting set alphabetically and not by length.

So a very nice and quick solution could be created using the DATALENGTH keyword. The following snippet will bring back all distinct values (along with their respective length) and will sort them in descending order by length:

SELECT DISTINCT <COLUMN_NAME>,
      DATALENGTH(<COLUMN_NAME>) AS StringLength
FROM <TABLE_NAME>
GROUP BY <COLUMN_NAME>
ORDER BY StringLength DESC

You can also modify that to bring back only the biggest string in the column by using the following code:

SELECT DISTINCT TOP 1 <COLUMN_NAME>,
      DATALENGTH(<COLUMN_NAME>) AS StringLength
FROM <TABLE_NAME>
GROUP BY <COLUMN_NAME>
ORDER BY StringLength DESC

So that was it... nice and easy and you can get the longest string in a column and the number of characters.

Hope that was useful!!!