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 |
-- 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
firstname ShortestLength
----------------------------------- ----------------------------
Tom 3
(1 row(s) affected)
firstname LongestLength
----------------------------------- -------------
Scarlett 8
(1 row(s) affected)
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)
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!
Comments