Examples of SQL Server SUBSTRING function to split a text column or a variable with string, start and length as arguments.
Find a few practical example queries that uses the SQL Server SUBSTRING function. This popular function is designed to cut text in T-SQL.
It is definitely one the most used integrated text functions in T-SQL. To make the most of the information from this tutorial, let’s explore different use cases of the Substring() function with practical code examples.
Table of Contents
What is the SQL Server SUBSTRING function?
First, the SUBSTRING function is equivalent to the SUBSTR function in non-Microsoft SQL languages. It simply allows you to split a string of characters using a start position and the length.
Indeed, you must specify the starting position and the length of the extracted substring. The generic syntax of the function is as follows:
SUBSTRING(string, start, length)
The function uses 3 mandatory arguments which are the following:
- The string: The text to be split, the supported types are: characters, binary, text, ntext or the image type, it can of course be contained in a T-SQL variable.
- The start position: i.e. from which character the slicing starts.
- and the length: i.e. number of characters that the SubString function returns.
If one of the arguments is missing, the MS SQL system returns this error, on an English SQL Server version of the Microsoft RDBMS.
“The substring function requires 3 argument(s).“
Use case 1 : Cut a hardcoded string
This query returns the first 3 letters of this string : ABCDEFGH which contains 8 letters.
SELECT SUBSTRING('ABCDEFGH', 1, 3) AS Result;
-- Result displayed
ABC
This other example shows how to use the second argument, i.e. the start character), this time with the 0 value. The SQL database management system 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 including Substring
It is of course possible to reuse the result directly in another text function. For example, using the system date from the server, called with GetDate() function combined with the Month argument.
- The first query displays the name of the current month in full: August
- The second one only returns the first 3 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 allows to manipulate date and month name formats. For example to display the month name in full letters using a simple query.
Use case 3: Use SQL Server Sub String function with variables
For the 3rd use case, for more readability, modularity, and reusability, it is a good practice in IT development to use variables in the code.
This code returns a variable with the name of the day in all caps with only the first 3 letters of the current day. It simply shows how combinations can allow more complex operations.
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 very common use case is to extract a substring from the very beginning of a string. It is then a prefix.
For example, we want to extract the first three characters of very name stroed in an Employee table. We can use the following T-SQL code to accomplish this:
SELECT SUBSTRING(name, 1, 3) FROM employee;
This will simply return a list of the first three characters of all employees.
Use Case 5 : Dynamically extract text from the end of a column
Another common use case of the SUBSTRING function is to extract a substring this time starting from the end of a string.
Let’s say we have a table of product codes and we need to extract the last three characters of each code. But the product codes can have different legnths. We can use the following T-SQL code to extract the suffixes:
SELECT SUBSTRING(code, LEN(code) - 2, 3) FROM products;
Use Case 6 : Extract a string from the middle of a SQL Server column
The SUBSTRING function can also be used to extract a text, dynamically from a specific character. Given a column with email addresses and we need to extract only the domain name from each address.
It is again a suffix, but this time with a dynamic start, and a dynamic length too.
expert-only@expert-only.com will become then expert-only.com
SELECT SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email) - CHARINDEX('@', email))
FROM emails;
A few words to conclude on cutting strings with SQL Server
In conclusion, the SUBSTRING function is a powerful and very useful T-SQL function. It allows users to extract, replace within strings, and perform other useful operations on text and char columns in general.
Hopefully those examples based on real life scemarios will help understand it and easily reproduce patterns on your own SQL projects!
Text functions, like time functions, are really central in SQL development and therefore to our SQL Server learning path.
Indeed, they help manipulate data and display different parts of dates in various formats. It is therefore important to adapt the data types used in the database. Here is a tutorial on how to manage long strings in T-SQL variables.
If you like the content, please subscribe to our Youtube channel to help us grow :
Be the first to comment