SQL Server Substring Function [9 Examples]

In this SQL Server 2019 tutorial, we will discuss what is SQL Server Substring function, SQL Server Left Substring, and will cover the below topic.

• SQL Server Substring
• SQL Server Substring before character
• SQL Server Substring after character
• SQL Server Substring last 4 character
• SQL Server Substring between two character
• SQL Server Substring vs LIKE performance
• SQL Server Substring index
• SQL Server Substring Charindex
• SQL Server Left Substring
• SQL Server Substring in where clause

SQL Server Substring Function

In SQL Server, substring() is a SQL function that is used to extract a specific part from any particular string based on user requirements. The substring function extracts a string with a predefined length, starting from a given location in an input string.

Syntax for SQL Server Substring()

SUBSTRING( expression, start, length )

• In the above syntax, the expression can be defined by any character, text, image, or binary value. It is the source string from where we will try to extract substrings using the substring() function.
• The start in the above syntax is an integer field that is used to define the position in the input string or the expression from where the new substring should start.
• If the start defined is less than 1, then the resulting expression will start with the first character in the expression. And If the start defined is greater than the number of characters in the input expression, a zero-length expression is returned.
• The length in the above syntax is a positive integer field that is used to represent the total expected length of the resulting substring from a given string.
• If the sum of start and length exceeds the number of characters in expression, then the entire input expression is returned, starting from the start.

How to use Substring() function in SQL Server

For the examples, I have used here, sql server 2019 and sql server management studio.

1. Without using table data in SQL Server

SELECT SUBSTRING('SQLSERVERGUIDES',1,3) AS NewString;

In the above example, we have defined the expression as “SQLSERVERGUIDES” from which we want to find a substring of length 3 starting from the 1st character in the expression. And we also want the result to appear in the NewString column. it will return the following output.

Now, let take one more example for a better understanding.

SELECT SUBSTRING('United States of America',1,13) AS NewString;

In the above example, we have defined the expression as “United States of America” from which we want to find a substring of length 13 starting from the 1st character in the expression. And we also want the result to appear in the NewString column. it will return the following output.

2. Using table data to find substring

For this demonstration, we are taking a random data table in SQL Server which is shown below.

Now from the above data, we will be using the first_name column and try to find the first 2 characters from it by using the following query.

SELECT first_name,SUBSTRING(first_name, 1, 2) AS NewString
FROM RandomData;

In the above example first, we are selecting the first_name column and then selecting the substring of the first 2 characters from the first_name column. And also assigning the substring result to a new column named NewString. It will return the following output.

This is how to use Substring() function in SQL Server 2019.

SQL Server substring charindex

In SQL Server CHARINDEX() is a simple function that is used to search the position of a substring inside a given input string. And this function starts the search of the substring from a specified location and returns its starting position. The Charindex() is not case-sensitive in nature.

Syntax for SQL Server Charindex()

CHARINDEX( expressionToFind , expressionToSearch [ , start_location ] )

• In the above syntax, expressionToFind is a substring expression whose starting position a user wants to find from a given input string. And it can have up to 8000 characters.
• The expressionToSearch in the above syntax is used to define the input string from which the substring expression will be searched.
• The start_location in the above syntax is an integer field that is used to define the position in the given input string from where the search has to start. And it is an optional parameter. If the start_location is negative, zero(0), or not defined then the search starts from the beginning of the given input string.
• If CHARINDEX() do not able to find expressionToFind within expressionToSearch, it will return 0.

SQL Server substring charindex example-1

SELECT CHARINDEX('g', 'sqlserverguides') AS MatchPosition;

In the above example, we have defined the expressionToSearch as “sqlserverguides” and expressionToFind as “g“. So we are trying to find the starting position of character “g” in the given expression “sqlserverguides“. And also assigning the result to a new column named MatchPosition.

The above query will return the following result.

SQL Server substring charindex example-2

SELECT CHARINDEX('r', 'New York') AS MatchPosition;

In the above example, we have defined the expressionToSearch as “New York” and expressionToFind as “r“. So we are trying to find the starting position of character “r” in the given expression “New York“. And also assigning the result to a new column named MatchPosition.

The above query will return the following result.

This is how to use SQL server substring charindex.

How to extract substring before or after a character

Now there can be scenarios where we either have to find a substring before a certain character or to find a substring after a character in a given expression. And to achieve these results we can use the combination of both Substring() and Charindex() functions.

The Substring function first finds a certain part in a given string and then returns it. Whereas the Charindex function finds and returns the starting position of a substring.

SQL Server Substring before character

Let’s understand “SQL Server Substring before character” with a small example, for this demonstration, we are using the same random sample table in SQL Server but now we are going to use the email field from it. And try to extract the substring before the “@” in the email field.

SELECT email, SUBSTRING(email, 0, CHARINDEX('@', email, 1)) AS Name
FROM RandomData

Now for better understanding let’s break the above query in 3 parts

1. Select statement
2. Substring Function
3. Charindex Function

First, we are using the Charindex function to find the position of “@” for each email entry in the table. And then we are using that position output as the length of the substring in the Substring function. In the end, we are using the SELECT statement to select the original email field and the substring email field as a result of the query. It will return the following output.

SQL Server Substring after character

Now for the “SQL Server Substring after character” demonstration we are going to extract the substring after the “@” character in the email field. And for this, we also have to use the LEN() function which returns the total length of an input string.

SELECT email, SUBSTRING(email,CHARINDEX('@', email)+1,LEN(email))
FROM RandomData

Again, let’s break the above query in 4 parts

1. Select Statement
2. Substring Function
3. Charindex Function
4. LEN() Function

First, we are using the Charindex function within the Substring function to find the exact position of the “@” character for each email entry. And then we are adding 1 into it so to define the start of the substring after the “@” character.

After this, we are using LEN() function to define the length of the substring. In the last, we are using the SELECT statement to select the original email field and the substring email field as a result of the query. It will return the following output.

This is how to extract substring before or after a character in sql server 2019.

SQL Server Substring last 4 characters

In SQL Server to extract a substring of the last 4 characters, we can use the RIGHT() function.

SQL Server RIGHT Substring Function

The Right() function in SQL Server is used to extract a certain number of characters from a given input string starting from the right.

It has the following syntax.

RIGHT( character_expression , integer_expression )  

• The Right() function accepts 2 arguments, the character_expression, and the integer_expression.
• The character_expression is the set of characters or a string from where the user wants to extract the substring. It can be a column, variable or constant.
• The integer_expression is an integer value that is used to define the number of characters to be extracted from the right of character_expression.
• If the integer_expression is negative it will return an error. And if integer_expression is greater than the number of character_expression it will return character_expression only.

Example For Right Substring Function

SELECT RIGHT('SQLServerGuides', 4) AS Last_4_Characters;

In the above example, we have defined the character_expression as “SQLServerGuides” and the integer_expression as 4 which means, that this query will return the last 4 characters from the “SQLServerGuides” expression. And we also want the result to come under the Last_4_Characters columns. After execution, we will get the following result.

This is how to substring last 4 characters in sql server 2019.

SQL Server Substring between 2 characters

In SQL Server, if a user wants to extract a substring between 2 different characters there is no direct function available. So to achieve this result, we have to use the combination of Substring and Charindex functions. Let’s understand the implementation with the help of an example.

For demonstration purposes let’s consider the following random code column which consists 6 digits random code starting from “$” character and ending at “#”.

Now we will try to extract the code between “$” and “#” using the following query.

SELECT Code, SUBSTRING(
       Code,
       CHARINDEX('$', Code)+1
       CHARINDEX('#', Code)-CHARINDEX('$', Code)-1
       ) AS Result
       FROM RandomCode

In the above example, first, we have defined the input expression in the Substring() function as Code which is a column containing random codes. After that, we are using the Charindex() function to first find the position of “$” and then, using the “$” position we are defining the start of the substring to be one more than the position of “$”.

And then, we are using the position of both “#” and “$” characters to define the length of a substring in the Substring() function. In the last, we are using the SELECT statement to project the original column and the new substring column.

We will get the following output from the above query.

This is how to substring between 2 characters in sql server 2019/2017.

Read: SQL Server Convert String to Date

SQL Server Substring to end of string

Now to extract a substring that goes till the end of the originally given input string, we have to define the length of the original string as the length parameter in the Substring() function. And for this, we can use the LEN() function.

The LEN() function is used to get the total length of an given input string.

For Example:

SELECT SUBSTRING('SQLServerGuides', 4, LEN('SQLServerGuides'))

In the above query, we have given “SQLServerGuides” as an input expression and then we define the start of the substring should be from the 4th character. In the last, we have used the LEN() function to define the total length of the substring. So after execution, it will return the following output.

Read: Exception Handling in SQL Server

SQL Server Substring Index

In SQL Server, there are mainly two functions that are used to find an index or position of a particular substring.

1. Charindex() Function
2. Patindex Function

The CharIndex and PatIndex functions are both classified as string functions in SQL Server, with some minor differences. And both functions accept 2 arguments. Both functions first search for the substring in a given string expression and then return the position as an integer value.

These functions appear to achieve the same thing on the surface, and in many circumstances, we can use whichever one we want.

However, there are a few characteristics that may influence the function we select in specific situations. The following is a summary of these:

1. Charindex() Function: This function is used to search an expression within another expression and it returns the starting position of the first expression if found. This function also has an optional third argument in which a user can specify the starting position of the search. However, the Patindex() function does not have any third argument.

Syntax for Charindex() function

CHARINDEX( expressionToFind , expressionToSearch [ , start_location ] )

Example for Charindex() function:

SELECT CHARINDEX('L', 'SQLServerGuides', 1) AS Result

2. Patindex() Function: This function is used to search a particular pattern in a given input expression. And it returns the starting position of the first occurrence of a pattern. If this function does not found any pattern in a valid expression, then it will return zero (0).

The Patindex() function also allows users to use the wildcard characters(%, _, ^) to search for any pattern. However, the Charindex() function does not support wild card characters.

Syntax for Patindex() function:

PATINDEX( '%pattern%' , expression )

Example for Patindex() function:

SELECT PATINDEX('%s_r%', 'SQLServerGuides') AS Result

Read Arithmetic operators in SQL Server

SQL Server Substring vs Like Performance

LIKE in SQL Server

• LIKE is an operator in SQL Server which is used to search a specific pattern in a given string expression.
• The pattern in the LIKE operator can include regular characters as well as wildcard characters. The Regular characters should match the exact characters given in the character string. Whereas, wildcard characters can match any piece of the character string.
• The LIKE operator is more flexible than the “=” and “!=” string comparison operators because we can use wildcard characters in it. And it returns a boolean expression (TRUE / FALSE) whenever a pattern match is found.

Syntax for LIKE Operator

expression LIKE pattern [ ESCAPE 'escape_character' ]

The expression in the above syntax can be any given character expression from where a user wants to extract a pattern.

The pattern is a specific string of characters that is used to search in a given expression. The maximum size of a pattern is 8,000 bytes. And we can use 4 different types of wildcard characters in it. Each wildcard character has the following significance:

Wildcard Character	Explanation
%	It allows us to match any string of any length (even a string of zero length)
_	It allows us to match on a single character
[ ]	It allows us to match on any character which is specified within the [ ] brackets (for example, [abc] will match on a, b, or c characters)
[^]	It allows us to not match on any character specified within the [^] brackets (for example, [^abc] would match on any character that is not a, b, or c characters)

The escaped_character in the above syntax is an optional parameter. It helps to test for literal instances of a wildcard character such as “%” or “_“.

Example for LIKE Operator

SELECT * FROM Mock_data WHERE last_name LIKE 'M%';

In the above example, we are using the % wildcard character to find all of the entities whose last_name begins with ‘M‘.

Substring in SQL Server

The Substring() is a function that is used to extract any specific part from a particular string based on user requirements. The substring function extracts a substring with a predefined length, starting from a given location in a given string expression.

The detailed description related to SQL Server Substring is already being mentioned at the starting of this post.

Major Performance Difference between Substring() and LIKE

• We can use the wildcard characters in the LIKE operator but we cannot use it in the Substring() function.
• The Like operator returns a boolean expression ( TRUE or FALSE ) whereas Substring returns a character expression or a substring from the original one.
• Due to the use of wildcard characters in the Like operator, the Like operator takes more query execution time than the substring function.

Read: How to create functions in SQL Server Management Studio

SQL Server LEFT Substring Function

The LEFT() is a substring function that is used to extract some characters from a given string expression starting from the left or the starting. And it has the following syntax.

LEFT( character_expression , integer_expression ) 

• The Left() function accepts 2 arguments, the character_expression, and the integer_expression.
• The character_expression is the input string from where the user wants to extract the substring. It can be a column, variable or constant.
• The integer_expression is a positive integer value that is used to define the number of characters to be extracted from the left of character_expression.
• If the integer_expression is negative it will return an error. And if integer_expression is greater than the number of character_expression it will return character_expression only.

Example For Left Substring Function

SELECT LEFT('SQLServerGuides', 3) AS Result

In the above example, we have defines the character_expression as “SQLServerGuides” and the integer_expression as 3 which means, that this query will return the first 3 characters from the “SQLServerGuides” expression. And we also want the result to come under the Result columns. After execution, we will get the following result.

This is an example of SQL Server LEFT substring function.

SQL Server Substring function in where clause

We can also use the Substring() function within a where clause of the statement. And for this, we only need to know what to look for in a string to get started. Let’s understand this with an example.

So for demonstration, let’s consider the following random data table in SQL Server

Now we are going to extract the entities whose first_name ends with the “ta” character. And to implement the result we will run the following query.

SELECT DISTINCT first_name, last_name
FROM MOCK_DATA
WHERE SUBSTRING(first_name, LEN(first_name)-1, 2) = 'ta'

In the above query, we are using the Substring() function to extract the last 2 characters from the first_name column, and then we are using this substring function in the where clause to find the entities where the last 2 characters in the first_name column are equal to “ta“. After successful execution, it will return the following result.

This is how to use SQL Server Substring function in where clause.

You may like:

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

In this SQL Server 2019 tutorial, we have learned what is SQL Server Substring function, SQL Server Left Substring, and will cover the below topic.

• SQL Server Substring
• SQL Server Substring before the character
• SQL Server Substring after the character
• SQL Server Substring last 4 character
• SQL Server Substring between two character
• SQL Server Substring vs LIKE performance
• SQL Server Substring index
• SQL Server Substring Charindex
• SQL Server Left Substring
• SQL Server Substring in where clause