SUBSTRING function in SQL Server

February 2023 · 2 minute read

The article will explore and guide you how to use the SUBSTRING function in SQL Server to extract a substring from a specified string.

Describe

The SUBSTRING function in SQL Server allows you to extract a substring of the specified length starting from a position in the input string.

Syntax

To use the SUBSTRING function in SQL Server, we use the following syntax:

 SUBSTRING(string, start, length)

Parameters :

  • string: can be a string, variable or column you want to extract.
  • start: is an integer specifying the location where the substring starts to be returned. Note that the first character in the string is 1, not 0.
  • length: a positive integer that specifies the number of characters of the substring returned from the string.

    • Note :

  • If the length parameter has a negative value, SUBSTRING will have an error
  • If start + length > the length of the string , the substring will start from start and include the remaining characters of the string.
  • See also LEFT and RIGHT functions to extract a substring from a specified string.
  • The SUBSTRING function can be used in later versions of SQL Server: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005.

    • For example

    Take a look and explore some examples of SUBSTRING functions in SQL Server.

     SELECT SUBSTRING('QuanTriMang.com', 1, 4); 
    Result: 'Quan' 
    SELECT SUBSTRING('QuanTriMang.com', 5, 3); 
    Result: 'Tri' 
    SELECT SUBSTRING('QuanTriMang.com', 5, 15); 
    Result: 'TriMang.com'

    Previous lesson: STUFF function in SQL Server

    Next lesson: UPPER function in SQL Server

    ncG1vNJzZmismaXArq3KnmWcp51kwLau0q2poqaXYrO2usKtoKimXZ67br%2FQpWSsnaKrsrM%3D