SQL Server Substring query

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:

  1. 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.
  2. The start character: i.e. from which character the slicing should start.
  3. 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.

Be the first to comment

Leave a Reply

Your email address will not be published.


*