;

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

id

firstname

lastname

gender

city

1

Tom

Hardy

m

New York

2

Hugh

Jackman

m

Los Angeles

3

Scarlett

johansson

f

Manhattan

4

Chris

Hemsworth

m

Sydney

6

Harry

Potter

m

New York

7

Tom

Holland

m

New York

8

Sofia

vergara

f

Colombia

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

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
Output

firstname                           ShortestLength

----------------------------------- ----------------------------

Tom                                  3

 

(1 row(s) affected)

 

firstname                            LongestLength

-----------------------------------  -------------

Scarlett                               8

 

(1 row(s) affected)

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

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

firstname                           StringLength

----------------------------------- ------------

Scarlett                              8

Tom                                   3

 

(2 row(s) affected)

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



Comments

Recent Posts
Tags