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

Transform Your Career

Choose from our industry-leading programs designed for career success

NSDC Certified

Modern Software and AI Engineering Program

Master full-stack development with AI integration

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Modern Data Science and ML with specialisation in AI

Advanced data science techniques with AI specialization

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Advanced AIML with Specialisation in Agentic AI

Deep dive into AIML with focus on Agentic systems

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

DevOps, Cloud & AI Platform Engineering

Build and manage AI-powered cloud infrastructure

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

AI Engineering Advanced Certification by IIT-Roorkee

Premier AI engineering certification from IIT-Roorkee

3 MonthsDuration

AI-LedCurriculum

Career SupportSupport

Go to Program

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.