SUBSTRING() Function in SQL Server

SUBSTRING() is a function in SQL server that allows the user to extract a sub-string from any given string with a specified length starting from a given location in an input string.

Syntax

The following shows the syntax of the SUBSTRING() function:

Parameter values

In the above syntax:

• expression - can be a character, binary, text, or image expression
• start - an integer value that specifies the location where the sub-string starts (1-based indexing)
• length - a positive integer that specifies the number of characters to be returned

Rules for Using SUBSTRING() Function

• The first character in the input expression is 1 not zero.
• If the start parameter is greater than the length of the expression, then nothing is returned as output.
• An error is raised if the length parameter is negative. - - If start + length > length of expression, in this case, the output sub-string will be the string starting from the start position till the end of the original string.

Return Types of SUBSTRING() Function

The returned string is the same type as the specified expression with the exceptions shown in the table:

Examples of SUBSTRING() Function

1. Using SUBSTRING() Function with a character string

The following example demonstrates how to return only a specific part of a string.

Output

In the above program, we are extracting a substring of length 5, starting from the first character in string Scaler.

2. Using SUBSTRING() with a character string with length parameter having a value greater than the maximum length of the expression

Output

In the above program, the substring length is greater than the maximum length (5) of the Scaler string expression. The query runs with no error and returns the full substring starting from the start position.

3. Using the SUBSTRING() function with table columns in a database

We can consider the following Players table having two columns jersey_num and name. Our task is to extract the last name of the player from the given table.

The Players table is as follows:

Output

In the above program, first, we used the POSITION() function to search the position of ' ', i.e. an empty character in the name column as the last_name will start after the ' ' character. Then, we used the result of the POSITION() function to determine the following: - The starting location of the last name: POSITION(' ' IN name) + 1 - The length of the last name: LENGTH(name) - POSITION(' ' IN name)

4. Using the SUBSTRING() function with text, ntext, and image data

The below example shows how we can use the SUBSTRING() function to extract characters from both text and image data columns in the pub_info table of the pubs database.

To run the following examples, you must install the pubs database. The Pubs database provides a set of fictional pieces of information about publishers, authors, titles and sales of books.

Output

The first ten characters from each of text and image data columns of the pub_info table are extracted. Text data is returned as varchar, and image data is returned as varbinary.