SQL Query to Find Shortest & Longest String From a Column of a Table
Last Updated : 13 Apr, 2021
Here, we are going to see how to find the shortest and longest string from a column of a table in a database with the help of SQL queries. We will first create a database "geeks", then we create a table "friends" with "firstName", "lastName", "age" columns. Then will perform our SQL query on this table to retrieve the shortest and longest string in a column.
For this article, we will be using the MS SQL Server as our database.
Creating a Database :
Use the below SQL statement to create a database called geeks:
CREATE DATABASE geeks;
Using Database :
USE geeks;
Table Definition:
We have the following Employee table in our geeks database :
CREATE TABLE friends( firstName VARCHAR(30) not NULL, lastName VARCHAR(30) not NULL, age INT NOT NULL);
You can use the below statement to query the description of the created table:
EXEC SP_COLUMNS friends;
Adding Data to Table:
Use the below statement to add data to the friends table:
INSERT INTO friends values ('Ajit','Yadav', 20), ('Ajay', 'More', 21), ('Amir', 'Jawadwala', 21), ('Zara', 'Khan', 20), ('Yogesh', 'Vaishnav', 21), ('Ashish', 'Yadav', 21), ('Govind', 'Vaishnav', 22), ('Vishal', 'Vishwakarma', 21); To verify the contents of the table use the below statement:
SELECT * FROM friends;
Now let's find the shortest and longest firstName from the table we have just created using char_length(), min(), and max() functions and LIMIT clause.
The shortest firstName :
Use the ow syntax to find the shortest firstName in the friends table:
SYNTAX :
SELECT TOP 1 * FROM<table_name> --Here we want only one row that's why TOP 1 *
WHERE
len(<string_column>) =
(SELECT min(len(<string_column>)) FROM<table_name> ) ;
Example :
SELECT TOP 1 * FROM friends WHERE len(firstName) = (SELECT min(len(firstName)) FROM friends);
Output :
The row with lexicographically shortest firstName :
If there are more than one strings of the same minimum length, and we want to retrieve, lexicographically, the shortest string then can do as following:
SYNTAX :
SELECT TOP 1 * FROM<table_name> --Here we want only one row that's why TOP 1*.
WHERE
len(<string_column>) =
(SELECTmin(len(<string_column>)) FROM <table_name> )
ORDER BY <column_name>; --In this case column_name would be firstName.
Example :
SELECT TOP 1 * FROM friends WHERE len(firstName) = (SELECT min(len(firstName)) FROM friends) ORDER BY firstname;
Output :
The row with longest firstName :
SYNTAX :
SELECT TOP 1* FROM<table_name>
WHERE
len(<string_column>) =
(SELECT max(len(<string_column>)) FROM <table_name> );
Example :
SELECT TOP 1 * FROMfriends WHERE len(firstName) = (SELECT max(len(firstName)) FROMfriends);
Output :
The row with lexicographically longest firstName :
SYNTAX :
SELECT TOP 1* FROM<table_name>
WHERE
len(<string_column>) =
(SELECT max(len(<string_column>)) from <table_name> )
ORDER BY <string_column>; --here we would order data by firstName.
Example :
SELECT TOP 1* FROM friends WHERE len(firstName) = (SELECT max(len(firstName)) FROM friends) ORDER BY firstName;
Output :
Similar Reads
How to Find the Longest or Shortest Text String in a Column in Excel?
In this article, we will see how to find the longest or shortest text string in a column in Excel? Usually, for finding the longest or shortest string we can visit the all string in columns one by one and compare them to get results. This seems to work when you have less amount of data in an excel s
4 min read
SQL Query to Get Column Names From a Table
SQL stands for Structured Query Language. It is a language used to interact with the database, i.e to create a database, to create a table in the database, to retrieve data or update a table in the database, etc. SQL is an ANSI(American National Standards Institute) standard. Using SQL, we can do ma
2 min read
SQL Query to Find the Number of Columns in a Table
SQL stands for a structure query language, which is used in the database to retrieve data, update and modify data in relational databases like MySql, Oracle, etc. And a query is a question or request for data from the database, that is if we ask someone any question then the question is the query. S
4 min read
Using CASE in ORDER BY Clause to Sort Records By Lowest Value of 2 Columns in SQL
The CASE statement in SQL is a flexible tool for implementing conditional logic within queries, similar to IF-THEN-ELSE constructs in programming languages. The ORDER BY clause in SQL is used to sort the results of a query based on one or more columns, enhancing data readability and analysis. In thi
4 min read
How to Check if a Column Exists in a SQL Server Table?
In this article, we will look at how to check if a particular column exists in a database table or not. For checking the existence of a column we need to create the table first. So, let us create a table with some columns and data. Creating table: Syntax: CREATE TABLE table_name ( column1 datatype,
2 min read
SQL Query to Get Only Numbers From a String
As we know in an SQL database we can insert any type of data. Sometimes in the productions server, the data gets corrupted by two or more rows being merged and being saved in a column. In that case, we can extract the numeric part from that string and save it again. So in this article, we will learn
2 min read
Remove All Spaces From a String in SQL Server
There are scenarios of the occurrence of spaces before and after a string and we may need to remove/trim the spaces for our use. Let us see how it is getting handled in SQL Server. Till SQL Server 2016, we have the functions called SQL LTRIM and SQL RTRIM functions. The name itself implies that LTRI
3 min read
How To Find the Sum of Digits in a String in SQL Server?
Given a string with digits and characters. The task is to find the sum of digits in that string. So, let's start by creating a database first. Step 1: Create a Database. Query : CREATE DATABASE GFG Step 2: Use the GFG Database. Query : USE GFG Step 3 : Â a) Select each character as a row by traversin
1 min read
How to find last value from any table in SQL Server
We could use LAST_VALUE() in SQL Server to find the last value from any table. LAST_VALUE() function used in SQL server is a type of window function that results the last value in an ordered partition of the given data set. Syntax : SELECT *, FROM tablename LAST_VALUE ( scalar_value ) OVER ( [PARTIT
2 min read
How to Find Most Frequent Value in Column in SQL?
To find the most frequent value in a column in SQL, use the COUNT() function to get a count of each unique value, sort the result in descending order, and select the first value in the final results set. In SQL, sometimes we need to find frequent values in a column. Finding the Most Frequent Value i
1 min read