Learn SQL Server LEN() Function With 20 Exercises

The LEN() function is one of the most common string functions in most databases and programming languages. In any SQL environment, knowing how to use the LEN() function and when to use it is very important.

This practice questions and answers provided in this article helps you understand the LEN() function, where to use it, how to use it and when to use it.

In addition, you will learn how to use this string function with other important SQL functions and concepts.

Table of Contents show

LEN() Syntax

LEN(expression nvarchar(1)) returns int

The expression argument can be a constant, variable, or column of either character or binary data.

According to Microsoft, the LEN() function

requires at least 1 argument (expression)
and returns an integer value

Dataset

Download the Sample Superstore 2022 dataset to follow along. You can visit the datasets page to see other available download options.

Let’s get started.

Basic SELECT Query

1. Select the Product Names and the length of each Product Name. Sort the length of Product Names in descending order. Your result should not contain duplicates.

SELECT DISTINCT
	[Product Name]
	,LEN([Product Name]) AS [Product Name Length]
FROM 
[Orders]
ORDER BY [Product Name Length] DESC

2. Select the length of the following fields and give them appropriate aliases.

Order Date
Customer Name
Postal Code
Product ID
Product Name
Sales
Quantity
Discount
Profit

SELECT 
	LEN([Order Date]) AS [Order Date Length]
	,LEN([Customer Name]) AS [Customer Name Length] 
	,LEN([Postal Code]) AS [Postal Code Length]
	,LEN([Product ID]) AS [product ID Length]
	,LEN([Product Name]) AS [Product Name Length]
	,LEN([Sales]) AS [Sales Length]
        ,LEN([Quantity]) AS [Quantity Length]
        ,LEN([Discount]) AS [Discount Length]
        ,LEN([Profit]) AS [Profit Length]
FROM Orders

Filtering

3. Select the Product Names where the length of the Product Name is greater than 70. Sort the result by Product Name. Your result should not contain duplicates.

SELECT DISTINCT
	[Product Name]
	,LEN([Product Name]) AS [Product Name Length]
FROM 
[Orders]
WHERE 
       LEN([Product Name]) > 70
ORDER BY [Product Name]

4. Select Product Names where the length of Product Name is greater than 50 and Sales is greater than 100. Your result should not contain duplicates.

SELECT DISTINCT
	[Product Name]
	,LEN([Product Name]) AS [Product Name Length]
FROM [Orders]
WHERE 
       LEN([Product Name]) > 50
       AND Sales > 100
ORDER BY [Product Name]

5. Select Product Names where length of Product Name is greater than 60 and Region is in the East or West. Sort the result by Product Name. The result should not contain duplicates.

SELECT DISTINCT
	[Product Name]
	,LEN([Product Name]) AS [Product Name Length]
FROM [Orders]
WHERE 
       LEN([Product Name]) > 60
       AND Region IN ('East', 'West')
ORDER BY [Product Name]

6. Select Sub-categories and their lengths where the length of Product Name is greater than 50 and the length of Sub-Category is at least 7. Sort the result by the Sub-category length. The result should not contain duplicates.

SELECT DISTINCT
	[Sub-Category]
	,LEN([Sub-Category]) AS [Sub-Category Length]
FROM 
[Orders]
WHERE 
       LEN([Product Name]) > 50
       AND LEN([Sub-Category]) <= 7
ORDER BY [Sub-Category Length]

Conditional Statements

7. Select Product Names and Length Category for the length of Product Names. The result should not contain duplicates.

Category	Field Length
0 – 40	Short
41 – 80	Medium
above 80	Long

SELECT DISTINCT
	[Product Name]
	,CASE 
	WHEN LEN([Product Name]) <= 40 THEN 'Short'
	WHEN LEN([Product Name]) > 40 AND LEN([Product Name]) <= 80 THEN 'Medium'
	WHEN LEN([Product Name]) > 80 THEN 'Long'
	END AS [Length Category]
FROM 
[Orders]

Aggregation

8. What is the longest Product Name length, shortest Product Name length and average Product Name length?

SELECT 
	MAX(LEN([Product Name])) AS [Longest Length]
	,MIN(LEN([Product Name])) AS [Shortest Length]
	,AVG(LEN([Product Name])) AS [Average Length]
FROM [Orders]

9. What is the average length of Product Names between 51 and 90?

SELECT
	AVG(LEN([Product Name])) AS Average_Length
FROM [Orders] 
WHERE 
       LEN([Product Name]) BETWEEN 51 AND 90

10. Select the length of Product Name, sum of Quantity and sum of Sales grouped by the length of Product Name where Quantity is above 100. Sort the result by Sales descending.

SELECT
	LEN([Product Name]) AS [Product Name Length]
	,SUM(Quantity) AS [Total Quantity]
	,SUM(Sales) AS [Total Sales]
FROM 
[Orders]
GROUP BY
       LEN([Product Name])
HAVING 
       SUM(Quantity) > 100
ORDER BY 
       SUM(Sales) DESC

Window Functions

11. Select the City and highest Sale for every City length.

WITH CTE AS
(
SELECT
	City
	,Sales
	,LEN(City) AS [City Length]
	,RANK() OVER (PARTITION BY LEN(City) ORDER BY Sales DESC) AS [City Length Rank]
FROM [Orders]
)
SELECT 
City
,Sales
,[City Length]
FROM CTE
WHERE 
       [City Length Rank] = 1

Arithmetic Statements

12. List all cities where the length of the City name is an even number. The result should not contain duplicates.

SELECT DISTINCT
	City
	,LEN(City) AS [City Length]
FROM [Orders]
WHERE 
       LEN([City]) % 2 = 0
ORDER BY City

13. List all cities where the length of the City name is an odd number. The result should not contain duplicates.

SELECT DISTINCT
	City
	,LEN(City) AS [City Length]
FROM [Orders]
WHERE 
       LEN([City]) % 2 = 1
ORDER BY City

14. Select all Orders in the East Region where the length of the Product Name is greater than the length of the City, State and Postal Code.

SELECT 
	*
FROM [Orders]
WHERE 
       LEN([Product Name]) < LEN(CONCAT(City, [State], [Postal Code]))
AND Region = 'East'

Date Statements

15. Select Customer Name, Product Length Name, Order Date and New Order Date where the City is Yonkers. New Order Date is Order Date plus length of Product Name. The result should not contain duplicates.

SELECT DISTINCT
	[Customer Name]
	,LEN([Product Name]) AS [Product Name Length]
	,[Order Date]
	,DATEADD(DAY, LEN([Product Name]), [Order Date]) AS  [New Order Date]
FROM [Orders]
WHERE 
       City = 'Yonkers'

16. Select every City where length of the City is equal to the Order Date day. The result should not contain duplicates.

SELECT DISTINCT
	City
	,[Order Date]
FROM [Orders]
WHERE 
       LEN(City) = DAY([Order Date])

17. List all Orders where the difference in days between the Order Date and Ship Date is greater than the length of the City.

SELECT 
	*
FROM [Orders]
WHERE 
       DATEDIFF(DAY, [Order Date],  [Ship Date]) > LEN(City)

Common Table Expressions & Sub Queries

18. Select the shortest and longest City names. If there is more than one shortest or longest City name, choose the one that comes first when ordered alphabetically.

WITH 
ShortestName AS
(
SELECT DISTINCT TOP 1 
City, 
LEN(City) [City Length]
FROM Orders
ORDER BY LEN(City), City
), 
LongestName AS
(
SELECT DISTINCT TOP 1 
City, 
LEN(City) [City Length]
FROM Orders
ORDER BY LEN(City) DESC, City)

SELECT * FROM ShortestName 
UNION 
SELECT * FROM LongestName
ORDER BY [City Length]

19. For each City length, return the second longest City name. If there is more than one shortest or longest City name, choose the one that comes second when ordered alphabetically.

SELECT
City
,[City Length]
,O2.[Row Rank]
FROM(
	SELECT
		City, 
		[City Length]
		,RANK() OVER (PARTITION BY LEN(City) ORDER BY LEN(City), City) AS [Row Rank] 
	FROM 
	(
		SELECT DISTINCT
		City
		,LEN(City) AS [City Length]
		FROM Orders
	) O1
) O2
where [Row Rank]  = 2

20. Get the average length of the City names. Then list the cities with the average City name length. The result should not contain duplicates.

SELECT 
DISTINCT City, 
LEN(City) AS [Average Length]
FROM Orders
WHERE LEN(City) = (
      SELECT AVG ([City Length])
      FROM (
            SELECT LEN(City) AS [City Length] 
            FROM Orders
            GROUP BY LEN(City)
       ) A
)

Hope you enjoyed these exercises.

Happy Analyzing!