SQL Server SUBSTRING() Function

In SQL Server, the SUBSTRING() function is used to extract a substring from a specified string. This function is very common and can be used to extract a portion of a string.

Syntax

The syntax of the SUBSTRING() function is as follows:

SUBSTRING(string, start, length)

Where the parameters are defined as follows:

• string: The string from which to extract the substring.
• start: The position at which to start the extraction. The first character position is 1.
• length: The number of characters to extract.

Usage

The SUBSTRING() function is commonly used in scenarios where it is necessary to extract a portion of a string, such as extracting key information from a long string or analyzing a string.

Examples

Here are two examples of using the SUBSTRING() function.

Example 1

Assuming there is a table students with a field name that stores the names of the students, we want to extract the surnames of the students from this field.

SELECT SUBSTRING(name, 1, CHARINDEX(' ', name) - 1) AS surname
FROM students

In this example, the first parameter of the SUBSTRING() function is the string to be extracted, name. The second parameter is the position at which to start the extraction, which is the first character of the string. The third parameter is the number of characters to extract, which is from the first character of the string until the position of the space. The CHARINDEX() function is used to find the position of the first space in the string.

Example 2

Assuming there is a table orders with a field description that stores the descriptions of the orders, we want to extract the order numbers from this field.

SELECT SUBSTRING(description, CHARINDEX('Order #', description) + 7, 6) AS order_number
FROM orders

In this example, the first parameter of the SUBSTRING() function is the string to be extracted, description. The second parameter is the position at which to start the extraction, which is the position after the first occurrence of Order # in the string. The third parameter is the number of characters to extract, which is the length of the order number, which is 6 characters. The CHARINDEX() function is used to find the position of the first occurrence of Order # in the string.

Conclusion

The SUBSTRING() function is a very common function in SQL Server, and can be used to extract a substring from a specified string. Its syntax is simple and clear, and it can help us to work more efficiently when dealing with strings.