CODEWITHSUNDEEP
sqlsql-servermaxmaxlength
paa
paa

Reputation: 73

How to get the value of max length in sql server

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?

Upvotes: 6

Views: 24957

Answers (3)

Bohemian
Bohemian

Reputation: 425003

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.

Upvotes: 1

czioutas
czioutas

Reputation: 1068

the sql you use is correct, u just have to cast it to an nvarchar

Upvotes: 0

Vland
Vland

Reputation: 4262

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

Upvotes: 5

Related Questions