Microsoft SQL Server String Functions Substring

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

Returns a substring that starts with the char that's in the specified start index and the specified max length.

Parameters:

  1. Character expression. The character expression can be of any data type that can be implicitly converted to varchar or nvarchar, except for text or ntext.
  2. Start index. A number (int or bigint) that specifies the start index of the requested substring. (Note: strings in sql server are base 1 index, meaning that the first character of the string is index 1). This number can be less then 1. In this case, If the sum of start index and max length is greater then 0, the return string would be a string starting from the first char of the character expression and with the length of (start index + max length - 1). If it's less then 0, an empty string would be returned.
  3. Max length. An integer number between 0 and bigint max value (9,223,372,036,854,775,807). If the max length parameter is negative, an error will be raised.
SELECT SUBSTRING('This is my string', 6, 5) -- returns 'is my'

If the max length + start index is more then the number of characters in the string, the entier string is returned.

SELECT SUBSTRING('Hello World',1,100) -- returns 'Hello World'

If the start index is bigger then the number of characters in the string, an empty string is returned.

SELECT SUBSTRING('Hello World',15,10) -- returns ''


Got any Microsoft SQL Server Question?