Sql как найти самую длинную строку

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 :

Last Updated :
13 Apr, 2021

Like Article

Save Article

We can use a combination of the MAX and MIN functions in conjunction with the SQL length function to get the max or min character width of a column. As the LENGTH function is universally used in most relational databases, we can use that syntax for our examples. Finding the max or min length of a column in SQL is pretty straight-forward.

SQL Server databases use LEN or DATALENGTH to find field width. It also has its own function to find the maximum length of a column – COL_LENGTH.

Syntax for finding MAX column length for a single field

SELECT MAX(LENGTH(<column_name>)) AS MaxColumnLength FROM Table;

Syntax for finding MIN column length for a single field

SELECT MIN(LENGTH(<column_name>)) AS MinColumnLength FROM Table;

If we include any non-aggregate functions into our query then we need a GROUP BY clause. Also, we could replace the above syntax examples with LEN or DATALENGTH for SQL Server.

RELATED: GROUP BY – Count, Sum, and More

Finding String Length for Multiple Columns

Similarly to finding a single field length we can find multiple column lengths just as easily.

Syntax for finding MAX column length for a multiple fields

SELECT MAX(LENGTH(<column_1>)) AS MaxColLen1, MAX(LENGTH(<column_2>)) AS MaxColLen2 FROM Table;

Syntax for finding MIN column length for multiple fields

SELECT MIN(LENGTH(<column_1>)) AS MinColLen1, MIN(LENGTH(<column_2>)) AS MinColLen2 FROM Table;

Practical Examples of MIN and MAX Field Length

We can see the following practical example shows us how to write the SQL to include two column values.

The resulting output gives us our max string length for both columns. The longest field in our first name column is a length of 13 characters and the max length of our last name column is 12 characters.

Alternatively, we could also get the minimum values with the following sql syntax.

In this case our minimum values output for first name is 3 and last name is a length of 4.

Final Thoughts

Finding the max and min lengths of a column in sql can be very useful. This is especially true when data modeling for a new database and migrating data from another system. These functions provide us valuable information for data analysis as well. Just don’t forget to include GROUP BY clause if you decide to add in non-aggregate columns into your queries.