Substring() in SQL Server: How to use Function with Example

By : Richard Peterson Richard Peterson
Hours Updated

What is Substring()?

Substring() is a function in SQL which allows the user to derive substring from any given string set as per user need. Substring() extracts a string with a specified length, starting from a given location in an input string. The purpose of Substring() in SQL is to return a specific portion of the string.

Syntax for Substring()

SUBSTRING(Expression, Starting Position, Total Length)

Here,

  • The substring() in SQL server Expression can be any character, binary, text or image. Expression is the source string of which we will fetch substring as per our need.
  • Starting Position determines the position in expression from where the new substring should start.
  • Total Length of SQLserver substring() is the total expected length of result substring from the expression, starting from Starting Position.

Rules for using SUBSTRING()

  • All three arguments are mandatory in MSSQL substring() function.
  • If the Starting Position is greater than the maximum number of character in Expression, then nothing is returned in substring() function in SQL server.
  • Total length can exceed the maximum character length of the original string. In this case, resultant substring will be the entire string starting from Starting Position in expression till end character of Expression.

Below Diagram illustrate the use of SUBSTRING() function in SQL server.

Substring() in SQL Works
Substring() in SQL Works

T-SQL Substring Examples

Assumption: Assume that we have the table as ‘Guru99’ with two columns and four rows as displayed below:

T-SQL Substring Examples

We will use ‘Guru99’ table in further examples

Query 1: SUBSTRING() in SQL with length less than Total Maximum length of expression

SELECT Tutorial_name, SUBSTRING(Tutorial_name,1,2) As SUB from Guru99;

Result: Below diagram display substring of ‘Tutorial_name’ column as ‘SUB’ column

T-SQL Substring Examples

Query 2: SUBSTRING() in SQL server with the length greater than Total Maximum length of expression.

SELECT Tutorial_name, SUBSTRING(Tutorial_name,2,8) As SUB from Guru99;

Result: Below diagram display substring of ‘Tutorial_name’ column as ‘SUB’ column. Here in spite of

Substring length is greater than Total Maximum length of expression, and no error and query return full string returned.

T-SQL Substring Examples

Summary

  • The syntax is: SUBSTRING (Expression, Starting Position, Total Length)
  • All three arguments are mandatory in substr() in SQL server.
  • The Expression can be any character, binary, text or image for substring() query in SQL.
You Might Like: