How to Find the Shortest and the Longest String from a Column of a table in SQL Server

Tutorialsrack 09/12/2020 SQL SQL Server

In this article, you will learn how to find the shortest string and the longest string from a column of a table in an SQL server. There are various ways to find the shortest and longest string from a column of the table in SQL Server.

Here is an example to find the shortest string and the longest string from a column of a table. Let's take this table for our example and find the shortest firstname and the longest firstname with their respective length from the table.

Table: tbl_emp

Example 1: Shortest and Longest String from a column of a table

-- Shortest First Name
select top 1 firstname,len(firstname) as ShortestLength from tbl_emp order by len(firstname) asc, firstname asc

-- Longest First Name
select top 1 firstname,len(firstname) as LongestLength from tbl_emp order by len(firstname) desc, firstname asc

Example 2: UsingCTE, DenseRank() and LEN() Function

with cte as
(
select *, LEN(firstname) as StringLength, DENSE_RANK() over (partition by len(firstname) order by firstname) as dRank from tbl_emp
)

select firstname,StringLength from cte where dRank = 1 and StringLength = (select MIN(StringLength) from cte)
UNION
select firstname,StringLength from cte where dRank = 1 and StringLength = (select max(StringLength) from cte)

I hope this article will help you to understand how to find the shortest string and the longest string from a column of a table in an SQL server.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Related Posts