SUBSTRING function in SQL Server

Substring in SQL Server is the knowledge that every programmer needs to know when handling strings. Here it is What you need to know about the substring function in SQL Server.

SQL Server is very important in programming. It is a database management system developed by Microsoft. In fact, learning SQL Server is not difficult. You just need to start from the most basic knowledge and then advance.

Data processing is a core part of SQL Server. When handling data, you don't necessarily have to consider a database full of strange numbers or calculations.

But text is also data. Finding text data in databases is very common. Not only do you have to retrieve it, but you also have to edit it. This function allows you to do the above things called text functions.

If you want to master SQL Server, you should learn the basic functions. Quantrimang.com offers separate SQL Server lessons for beginners. You can find out at any time.

One of SQL Server's popular text functions is Substring(). The substring function in SQL Server has many practical applications. If you use them properly, you can run many cool functions for your product. Substring is also known as cutting SQL string. So Substring SQL What effect does it have? How to use this function? Let's find out with Quantrimang.com!

Describe

SUBSTRING function in SQL Server allows you to extract a substring of a specified length starting from a position in the input string.

Syntax

To use the SUBSTRING function in SQL Server, we use the following syntax:

SUBSTRING(string, start, length)

Parameter:

• string: can be a string, variable, or column that you want to extract.
• start: is an integer specifying the position where the substring begins to be returned. Note that the first character in string.string is 1, not 0.
• length: a positive integer specifying the number of characters of the substring returned from string.string.

Note:

• If parameter length. length If the value is negative, SUBSTRING will cause an error
• If start + length. length > length of string.string then the substring will start from start and include the remaining characters of string.string.
• See also LEFT and RIGHT functions to extract a substring from a specified string.
• The SUBSTRING function can be used in the following versions of SQL Server: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.

For example

Take a look and explore some examples of the SUBSTRING function in SQL Server.

SELECT SUBSTRING('QuanTriMang.com', 1, 4);
Result: ‘Quan’
SELECT SUBSTRING('QuanTriMang.com', 5, 3);
Result: ‘Tri’
SELECT SUBSTRING('QuanTriMang.com', 5, 15);
Result: ‘TriMang.com’

Previous article: STUFF function in SQL Server

Next article: UPPER function in SQL Server

Use SUBSTRING on nested queries

Suppose you want to use the SUBSTRING function on a nested query in the player_Details table, you can use the following SQL code:

Query:

SELECT SUBSTRING(subquery.PlayerName, 1, 3) AS ShortenedName, subquery.City
FROM (
SELECT *
FROM Player_Details
WHERE City = 'New York'
) AS subquery;

Result:

To select entire rows from the Player_Details table, where the City column is New York, the first SQL query creates a nested query. A derived table or subquery contains the results of the nested query, which is then used as input for the SUBSTRING function.

The PlayerName column in the subquery receives the SUBSTRING function, which selects the first 3 characters of each name. The result of this substring operation is that it is returned along with the City column from the subquery in a new column with the alias ShortenedName.

Use substring with a string of characters

The substring() function retrieves the string portion based on the starting position and the length is optional.

Query:

SELECT SUBSTRING(PlayerName, 1, 5) AS ExtractString
FROM Player_Details;

Result:

Query:

SELECT SUBSTRING(PlayerName, 1, 3) AS ExtractString
FROM Player_Details;

Result:

Column PlayerName In the subquery that receives the substring function, select the first 3 characters of each name. The result of this substring operation returns the column PlayerName from subquery in new column with alias ExtractString.

Rules for using SUBSTRING() to remember

• All 3 arguments are required in the MS-SQL substring() function. If the starting position exceeds the maximum number of characters in the expression, the substring() SQL function returns an empty result.
• The total length may exceed the maximum character length of the original string.
• In this case, the resulting substring is the entire string from the start to the end of the expression.