SubString in SQL Server

Have you ever needed a specific portion of a string in SQL Server? Is that possible? Yes, we can retrieve a few characters from the column of a string by substring.

This helpful feature allows you to change strings, easily adding flexibility to database queries. This tutorial will explain substring in SQL Server, including syntax, usage, optimization tips, and examples.

What is the SubString Function in SQL Server

With SQL, the SUBSTRING function helps extract a specific string section based on predefined criteria. Whether we want to extract a substring based on a pattern, a particular number of characters, or a substring from a specific position, it enables us to manipulate and analyze textual data efficiently.

Syntax of SubString in SQL

Below is the syntax of SubString in SQL Server.

• string_expression – The input string used to extract the substring.
• start_position – The substring’s starting location within the input string. Usually, it’s an integer.
• Length (Optional) – The desired length of the substring to be extracted. If omitted, the substring will be extracted from the start_position to the end of the input string.

SUBSTRING(string_expression, start_position, length)

Points to know – The start_position parameter is typically 1-based, which means that position 1 corresponds to the input string’s first character.

SQL SubString Function Example

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

• SUBSTRING – Keyword
• jeniferpeter – Name of the string which I want substring
• 1,5 – First 5 char

The result will be produced as below.

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

SubString Function with Literal String

It’s straightforward to use the SQL SUBSTRING function with literal strings. Just enter the desired values in the syntax.

Consider this SQL SUBSTRING function command,

SELECT SUBSTRING ('SQL In server', 6, 11) AS ExtractString 

It will take the original string ‘SQL In Server’ and extract a substring beginning with the 6th character and extracting a length of 11 characters. Along with the substring, the column with ExtractString will be returned.

The SUBSTRING function extracts a portion of a string based on a predefined starting position and length. The function’s first argument is the original string, and the second argument, in this case, the sixth character, specifies the starting character.

The third argument specifies the length of the substring to be extracted, which in this case is 11 characters.

This SQL query extracts a specific portion of a longer string that is required for reporting or additional analysis. It can be helpful when working with large datasets and extracting and manipulating particular information from a lengthy text string.

SubString Function with Table

Let’s consider a table and use the Substring function for it. Here, I have taken EmpDetails as a sample table.

Below are my table details. Here, I want to use a substring function for EmpName.

SELECT SUBSTRING(EmpName, 1, 3) AS ExtractString
FROM EmpDetails;

Look at the below output after using SubString. The SUBSTRING function is applied to the EmpName column in the subquery, selecting the first three characters of each name.

This substring operation returns the EmpName column from the subquery in a new column with the alias ExtractString.

SubString on Nested Queries

You can use the below SQL code, assuming you want to use the SUBSTRING function on a nested query inside the EmpDetails table.

This SQL query creates a nested query to select all rows from the EmpDetails table where the Role column equals “QA.” The SUBSTRING function is then called on a derived table or subquery containing the output of this nested query.

The SUBSTRING function is applied to the EmpName column in the subquery, selecting the first three characters of each name. This substring operation is then returned in a new column with the alias ShortenedName and the Role column from the subquery.

SELECT SUBSTRING(subquery.EmpName, 1, 3) AS ShortenedName, subquery.Role
FROM (
    SELECT *
    FROM EmpDetails
    WHERE Role = 'QA'
) AS subquery;

Points to Consider about SQL SubString Function

• A substring can be extracted from a string using the SUBSTRING() function, starting at a given position and having an optional length.
• It can be applied to table columns or literal strings.
• The SUBSTRING() function is also implemented by the LEFT() and RIGHT() functions.
• When SUBSTRING() is used in the WHERE clause, query performance is adversely affected because the function must be run for every row.

SubString Function Rule

• The SQL substring() method requires all three arguments. The SQL Server substring() function returns null if the expression’s maximum character count is exceeded at the starting position.
• The overall length may surpass the original string’s maximum character limit.
• In this situation, the resulting substring is the entire string from the expression start position to the expression end character.

Conclusion

SQL’s Substring function is essential for working with strings, extracting particular segments, and transforming data. You can fully use the Substring function in your SQL queries by becoming familiar with its syntax, how to use it, and performance optimization strategies.

Understanding this simple tutorial enables you to approach string manipulations with assurance and derive insightful information from your database.

You may like:

• SQL Server Convert Function
• SQL Server convert integer to string