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

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.

T-SQ Substring Examples:

Assumption: Assume that we have the table as 'gtupapers' with two columns and four rows as displayed below:

We will use 'gtupapers' 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 gtupapers;

Result: Below diagram display substring of 'Tutorial_name' column as 'SUB' column

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 gtupapers;

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.

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.