How to get the value of max length in sql server

Question

i would like to ask you if there is a way to get the value of maxlength of column in ms sql server.
For example in the table A we have:

id  | value
----+--------
 1  | 0123
 2  | 00034567
 3  | 547

The desired result for this data set is 00034567.

Wherever i have searched for this problem i get the answer select max(len(value)) which is not what i need, because it gives the max number of characters of the value and not the value itself.

Any ideas?

Answer 1

SELECT TOP 1 t.value
FROM table AS t
ORDER BY LEN(t.value) DESC

Answer 2

Sort by length and take the first row:

select top 1 value
from mytable
order by len(value) desc

If you want ties:

select value
from mytable
where len(value) = (select max(len(value)) from mytable)

Or more efficient but SQL Server specific:

select value from (
  select value,
  rank() over (order by len(value) desc) rank
  from mytable) x
where rank = 1

See SQLFiddle for last one.