SQL Server SUBSTRING() Function

The SQL Server SUBSTRING function extracts a substring from a string, starting at a specified position and with an optional length.

The SUBSTRING function also works in Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse.

Syntax

The SQL SUBSTRING function syntax is:

SUBSTRING(input_string, start, length);

Parameters

• Input_string: It can be a character, binary, text, or image expression.
• Start: An integer defines the location where the returned substring starts. The first position in the string is 1.
• Length: A positive integer specifies the number of characters to be returned from the substring.
• Returns: It returns a substring with a specified length starting from a location in an input string. 

Rules for Using SUBSTRING() Function in SQL

1. All three arguments are required in the SQL substring() function. If the starting position exceeds the maximum number of characters in the expression, the SQL Server substring() function returns nothing.
2. The total length can exceed the maximum character length of the original string.
3. In this case, the resulting substring is the entire string from the expression start position to the expression end character.

SQL Server SUBSTRING() Function Examples

Let's look at some examples of the SUBSTRING() function in SQL and understand how to use it in SQL Server.

In this example, we use the SUBSTRING function in SQL Server.

Query:

SELECT SUBSTRING('GeeksforGeeks',1,5);

Output:

👁 substring function example

SUBSTRING() Function with Literal Strings Example

Consider this SQL SUBSTRING function command:

SELECT SUBSTRING ('SQL In Geeksforgeeks', 7, 18) AS ExtractString 

It will take the original string 'SQL In Geeksforgeeks' and extract a substring beginning with the 7th character and extracting a length of 18 characters. A new column with the alias ExtractString will be returned along with the resulting substring.

Output

👁 substring() function with literal strings example output

SUBSTRING() Function With Table Columns Example

Let's create a table and use SUBSTRING() function on table column.

Query :

CREATE TABLE Player_Details (
 PlayerId INT PRIMARY KEY,
 PlayerName VARCHAR(50),
 City VARCHAR(50)
);
INSERT INTO Player_Details (PlayerId, PlayerName, City)
VALUES 
 (1,'John', 'New York'),
 (2,'Sarah', 'Los Angeles'),
 (3,'David', 'Chicago'),
 (4,'Emily', 'Houston'),
 (5,'Michael', 'Phoenix'),
 (6,'Ava', 'Philadelphia'),
 (7,'Joshua', 'San Antonio'),
 (8,'Sophia', 'San Diego'),
 (9,'Daniel', 'Dallas'),
 (10,'Olivia', 'San Jose');

Output:

👁 table created

In this example, we will use the SUBSTRING() function on a table column.

Query

SELECT SUBSTRING(PlayerName, 1, 3) AS ExtractString
FROM Player_Details;

Output

👁 substring() function with table columns example output

The PlayerName column in the subquery receives the SUBSTRING function, which chooses the first three characters of each name. The outcome of this substring operation is to return the PlayerName column from the subquery in a new column with the alias ExtractString.

Using SUBSTRING on a Nested Queries

Assuming you want to use the SUBSTRING function on a nested query within the player_Details table, you could use the following SQL code

Query

SELECT SUBSTRING(subquery.PlayerName, 1, 3) AS ShortenedName, subquery.City
FROM (
SELECT *
FROM Player_Details
WHERE City = 'New York'
) AS subquery;

Output

👁 img

Important Function About SQL SUBSTRING Function

• The SUBSTRING() function extracts a substring from a string, starting at a specified position and with an optional length.
• It can be used with literal strings or columns in a table.
• The LEFT() and RIGHT() functions are also implementation of SUBSTRING() Function.
• Using SUBSTRING() in the WHERE clause negatively impacts the query performance, as the function will be executed for each row.