How to use SQL Server SUBSTRING function? T-SQL

Use the SQL Server SUBSTRING function to split a text column or a variable, copy and adapt the examples using the string, start and length arguments.

Example queries on how to use the SQL Server SUBSTRING function to cut text in T-SQL. It is one the most used integrated text functions in T-SQL. SQL Server is a popular relational database management system that allows users to store and manipulate data. In this tutorial, we will explore the various use cases of the SUBSTRING function and provide examples of how to use it in T-SQL.

Table of Contents

What is the SQL Server SUBSTRING function?

First, the SUBSTRING text function is equivalent to the SUBSTR function in non-Microsoft SQL languages. It allows you to split a string of characters. And it is used to extract a portion of a string. It allows users to specify the starting position and length of the substring they want to extract. The syntax of the SUBSTRING function is as follows:

SUBSTRING(string, start, length)

The function uses 3 mandatory arguments which are the following:

The string: The text field to be split, the supported types are: characters, binary, text, ntext or the image type. The expression to be clipped can of course be contained in a T-SQL variable.
The start: i.e. from which character the slicing should start.
and the length: this is the number of characters that the SUBSTRING function returns, i.e. the maximum length of the text to be cut out.

If one of the arguments is missing, the MS SQL system returns this error:

“The substring function requires 3 argument(s).” on an English SQL Server version of the Microsoft RDBMS.

Use case 1: Cut a hardcoded string

This query returns the first three letters of the string ABCDEFGH which contains eight letters.

SELECT SUBSTRING('ABCDEFGH', 1, 3) AS Résultat;
-- Résultat
ABC

This other example shows how to use the second argument (i.e. the start character), this time with a negative value. The DBMS uses the larger of the numbers between start + length -1 and 0. So in our example : 0+3-1 = 2. So only the first two letters of the alphabet are displayed.

SELECT SUBSTRING('ABCDEFGH', 0, 3) AS Result;
-- Result
AB

Use case 2: Use nested SQL Server text functions with SUBSTRING

It is of course possible to reuse the result directly in a text function. For example, with the name of the SQL Server.

The first part of the query therefore displays the name of the current month in full: August
On the other hand, the second part only returns the first 4 letters of the month: Aug

SELECT DATENAME(MONTH, GETDATE());
-- Result
August
SELECT SUBSTRING(DATENAME(MONTH, GETDATE()), 1, 3) ;
-- Result
Aug

Of course, integrated T-SQL date functions also alloys to manipulate date and month name formats. For example to display the month name in letters using a simple query.

How to display the month name in SQL Server?

Use case 3: Use the SUBSTRING function with T-SQL variables

Finally, for even more readability, modularity, and reusability, it is a good practice in development to use variables in the code. Here is an example that returns a variable with the name of the day in all caps and that returns only the first 3 letters of the day of the week in text.

DECLARE @Day as NVARCHAR(20);
SELECT  @Day = DATENAME(WEEKDAY, GETDATE());
SELECT  UPPER(@Day); 

-- Result : day in all letters
THURSDAY

SELECT UPPER(SUBSTRING(@Day, 1, 3));

-- Result : three first letters of the weekday
THU

Use Case 4: Extract a substring from the beginning of a column

One common use case of the SUBSTRING function is to extract a substring from the beginning of a string. For example, suppose we have a table of employee names and we want to extract the first three characters of each name. We can use the following T-SQL code to accomplish this:

SELECT SUBSTRING(name, 1, 3)
FROM employee;

This will return a list of the first three characters of each name in the employee table.

Use Case 5: Extract a substring from the end of a column

Another common use case of the SUBSTRING function is to extract a substring from the end of a string. For example, suppose we have a table of product codes and we want to extract the last three characters of each code. We can use the following T-SQL code to accomplish this:

SELECT SUBSTRING(code, LEN(code) - 2, 3)
FROM products;

This code return a list of the last three characters of each code from products table.

Use Case 6: Extract a substring from the middle of a CHAR column

The SUBSTRING function can also be used to extract a substring from the middle of a string. For example, suppose we have a table of email addresses and we want to extract the domain name from each address. We can use the following T-SQL code to accomplish this, it will simply return a list of the domain names from each email address in the emails table.

SELECT SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email) - CHARINDEX('@', email))
FROM emails;

Conclusion on SQL Server SUBSTRING examples

In conclusion, the SUBSTRING function is a powerful T-SQL function that allows users to extract substrings from strings, replace substrings within strings, and perform other useful operations on strings. By understanding the syntax and various use cases of the SUBSTRING function, users can become more proficient in writing T-SQL queries for SQL Server.

Moreover, mastering the use of the SUBSTRING function can help developers optimize their SQL queries and improve the performance of their applications. When using the SUBSTRING function, it is important to keep in mind that the start position and length parameters must be specified correctly. If the start position is greater than the length of the string, the function will return an empty string. Similarly, if the length parameter is set to a value that exceeds the length of the string, the function will return the entire string.

In addition, developers should know that the SUBSTRING function is case-sensitive, which means that it will treat uppercase and lowercase letters as distinct characters. Therefore, if you want to extract a substring that may contain uppercase or lowercase letters, you may need to use the LOWER or UPPER functions to ensure that the search criteria are case-insensitive.

Text functions, like time functions, are really central in SQL development and therefore to our SQL Server learning path. They allow you to manipulate data and display different parts of the dates in different formats. It is important to adapt the data type to the text stored in the database. Here is a tutorial on how to manage long strings in T-SQL variables.