Substring function

The SQL Server SUBSTRING function is a powerful and commonly used string manipulation function that allows you to extract a substring from a given string. This function is particularly useful in SQL queries when you need to work with parts of a text field or column. The string function SUBSTRING has the role of extracting and returning characters from an expression. The type of expression can be: character, binary, text, ntext, or image.

Syntax

The basic syntax of the SUBSTRING function is as follows:

SUBSTRING(input_string, start_position, length)

input_string: This is the source string from which you want to extract a substring.
start_position: This parameter specifies the position within the input string where the extraction should begin. It is a 1-based index, meaning the first character is at position 1, the second character at position 2, and so on.
length: This parameter indicates the number of characters to extract from the input string.

Example

Here's an example to illustrate how the SUBSTRING function works in SQL Server:

SELECT SUBSTRING('Hello, World!', 1, 5) AS Result;

In this example, the SUBSTRING function extracts a substring from the input string 'Hello, World!' starting at position 1 (the 'H') and with a length of 5 characters. The result of this query will be 'Hello'.

The SUBSTRING function can also be used in more complex queries, often in combination with other string functions. For example, you can use it to extract parts of a column in a database table, manipulate data, or even construct more meaningful data representations.
Here's an example of using the SUBSTRING function with a database table:

SELECT
SUBSTRING(FirstName, 1, 1) AS FirstInitial,
SUBSTRING(LastName, 1, 3) AS First3Characters
FROM Employees;

In this query, the SUBSTRING function is applied to the FirstName and LastName columns of an "Employees" table, extracting the first initial of the first name and the first three characters of the last name.

It's important to be cautious when using the SUBSTRING function to ensure that the start position and length parameters are within the bounds of the input string. If you attempt to extract more characters than are available in the input string, you'll get an error. To avoid this, you can use the LEN function to determine the length of the input string and calculate the length parameter dynamically.

See also: T-SQL Functions -> Patindex | Replace | Right | Rtrim | Upper