SUBSTRING Function in SQL Server

by

In this SQL Server tutorial, you will learn about the SUBSTRING function in SQL Server. You can use this function to manipulate the string by extracting a specific part.

First, you will be introduced to the definition of the SUBSTRING() function and its syntax. Then, with a simple example, you will understand how it works.

By the end of this tutorial, you will use the SUBSTRING function in SQL Server on the table column.

SUBSTRING Function in SQL Server

The SUBSTRING function in SQL Server extracts the substring (characters) from the string (character expression) with a given length from the specified position.

So, if you need the string part from the specific starting index and with a certain length, use the SUBSTRING() function.

The syntax is given below.

SUBSTRING ( string, start, length )

Where,

  • SUBSTRING(): The function extracts the substring from the string based on the specified position in the string.
  • string: It is the mains string from which the substring is extracted. It can be any expression related to a string, such as ntext, character, binary, or image expression.
  • start: The position in the string from where the extraction begins.
  • length: The number of characters should be extracted from the specified position.

The return type of the SUBSTRING() is character data, which generally depends upon the data type of the specified string. It returns the varchar for string data type, char, varchar, and text; for the nchar, nvarchar, and ntext is nvarchar. For binary, varbinary, and image, it is varbinary.

  • Remember, if the NULL value is passed to the SUBSTRING(), it returns the NULL; when using the SUBSTRING(), handle the NULL values in your query.

Let’s see with an example how SUBSTRING in SQL Server extracts the substring from the string. Suppose you have the string ‘I am a database developer’, and you must extract the substrings from the specific index of 8 characters.

For that, use the query below.

SELECT SUBSTRING('I am a database developer', 7, 9) ExtractedSubstring;
SUBSTRING Function in SQL Server

From the result, you can see that the extracted substring is a database. Let’s understand the query part SUBSTRING(‘I am a database developer’, 7, 9).

Here, the source string is ‘I am a database developer’, 7 is the starting index or position from where the extraction starts, and 9 is the substring’s length, meaning the extracted substring should contain nine characters, including spaces.

This is how to use the SUBSTRING function in SQL Server to extract the substring from the string with a specified length from the specified position. Next, let’s use the SUBSTRING function with table columns.

Using SUBSTRING Function in SQL Server on Table Columns

In the above section, you have used the literal string in the SUBSTRING function in SQL Server, but here, you learn how to use the SUBSTRING function in SQL Server with table data to extract the required substring from the table columns.

For example, suppose you have an Events table with columns EventName and DateTime shown below.

Using SUBSTRING Function in SQL Server on Events Table Columns

The DateTime column contains the date and time for the events, and the column type is VARCHAR, but as you can see, the date is not very readable, so here, you have to separate the date and time. For that, you can use the query below.

SELECT EventName, DateTime,
	SUBSTRING(DateTime, 1, 8) AS Date,
	SUBSTRING(DateTime, 9, 5) AS Time
FROM Events;
Using SUBSTRING Function in SQL Server on Table Columns

As you can see in the above output, the date and time are extracted using the SUBSTRING () with the event name, and now it is more readable than before.

Understand the query part, SUBSTRING(DateTime, 1, 8) AS Date. This means starting the extraction of a substring from the index or position one (1) and extracting eight (8) characters from that index. As a result, the complete date is extracted from the values in the DateTime column.

Then, in the next query part, SUBSTRING(DateTime, 9, 5) means starting the extraction from index 9 and extracting the substring of length 5, meaning the total length of the substring should be 5 characters. As a result, complete time is extracted.

But you can extract the substring to make it more readable, such as year, month, day, hour, and minutes. Just run the query below.

SELECT EventName, DateTime,
	SUBSTRING(DateTime, 1, 4) AS Year,
	SUBSTRING(DateTime, 5, 2) AS Month,
	SUBSTRING(DateTime, 7, 2) AS Day,
	SUBSTRING(DateTime, 9, 2) AS Hour,
	SUBSTRING(DateTime, 12, 2) AS Minute
FROM Events;
Using SUBSTRING Function in SQL Server on Table Columns Multiple SUBSTRING()

Now, the date and time of the event are very readable; you can tell the exact date and time, such as year, month, day, hour, and minute.

This is how you can use the SUBSTRING in SQL Server with table columns to extract the substring based on the specified position and length.

Conclusion

In this SQL Server tutorial, you learned how to extract the substring from the string, where you have extracted the substring from the specified position and length. Then, using the SUBSTRING function in SQL Server, you extracted the date and time components, such as year, month, day, etc., from the datetime strings.

You may like to read: