Open In App

SUBSTRING() Function in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

The SUBSTRING() function extracts a substring starting from a position in an input string with a given length. In the case of the substring, you need an input string and need to mention the starting point and the total length of the string.

Syntax

SUBSTRING(input_string, start, length);

Parameter: SUBSTRING function accepts three-parameter like String, start, and length. Let’s have a look.

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

Now we will make a very simple example:

Query

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

Output

img

Rules for Using SUBSTRING()

  1. All three arguments are required in the MS-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 the SUBSTRING() Function with Literal Strings

The SQL statement SELECT SUBSTRING(‘SQL In Geeksforgeeks’, 7, 18) AS ExtractString 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.

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.

Query

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

Output

extract

Using the SUBSTRING() Function With Table Columns 

This SQL statement creates the Player_Details table, 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.

Table -Player_Details 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

IMG

Using SUBSTRING() with a character string

The SUBSTRING() function extracts the portion of the character string based on the starting position and the length is optional.

Query

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

Output

ExtractString

Query

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

Output

ExtractString

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

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.


Last Updated : 28 Aug, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads