SQL Server SUBSTRING() Function

SUBSTRING function in SQL Server is used to extract a substring from a string, starting at a specified position and with an optional length.

It is very useful when you need to extract a specific portion of a string for further processing or analysis.

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

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 – It is an integer defining the location where the returned substring starts. The first position in the string is 1.
• Length – It is a positive integer that 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. 

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

SQL SUBSTRING function Example

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

Query:

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

Output:

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.

Using SQL SUBSTRING function with literal strings is very easy, just put the desired values in the syntax.

SUBSTRING() Function with Literal Strings Example

Consider this SQL SUBSTRING function command:

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

It will take the original string ‘SQL In w3wiki’ 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.

To extract a section of a string based on a predetermined starting position and length, the SUBSTRING function is utilized. The original string is passed as the function’s first argument, and the second argument specifies the starting character, in this case, the seventh one. The third argument, which is 18 characters in this case, specifies how long the substring is to be extracted.

The purpose of using this SQL query is to extract a specific part of a longer string that is needed for further analysis or reporting. It can be useful when dealing with large datasets where we need to extract and manipulate specific information from a long string of text.

Output

To use the SUBSTRING() function with table columns we will first create a table “Player_Details“, which has three columns: PlayerId, PlayerName, and City. Since the PlayerId column is designated as the primary key, each row in the table will have a different PlayerId as its identifier.

Ten rows of data are added to the table using the INSERT INTO statement after the table has been created. While the PlayerId column will be automatically generated as a unique identifier for each row, the PlayerName and City columns have values specified for each row.

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:

SUBSTRING() Function With Table Columns Example

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

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.

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

In order to select all rows from the Player_Details table where the City column equals “New York,” this SQL query first creates a nested query. A derived table or subquery that contains the results of this nested query is then used as the input for the SUBSTRING function.

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 then returned alongside the City column from the subquery in a new column with the alias ShortenedName.

• 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.