What is the T-SQL syntax to split a text field with the SQL Server SUBSTRING query function? This function essentially allows you to split text and select only a part of a string.
Example of a SQL Server SUBSTRING query
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.
The function uses 3 mandatory arguments which are the following:
- The expression: 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 character: i.e. from which character the slicing should start.
- 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 system returns this error:
“The substring function requires 3 argument(s).” on an English SQL Server version of the Microsoft RDBMS.
Cutting 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
Nested SUBSTRING with other SQL Server functions
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: Augu
SELECT DATENAME(MONTH, GETDATE()); -- Result August SELECT SUBSTRING(DATENAME(MONTH, GETDATE()), 1, 4) ; -- Result Augu
Using the SUBSTRING function with T-SQL variables
Finally, for even more readability, modularity, and reusability, 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
Text functions, like time functions, are central to the development and therefore to the SQL Server tutorials. They allow you to manipulate data and display different parts of the dates in different formats.