MS SQL Server SUBSTRING() Function

Sometimes you may need to work with specific part of a expression in MS SQL Server. There is a function called SUBSTRING in MS SQL Server by which you can do this. SUBSTRING function returns part of a character, binary, text, or image expression in SQL Server. It is one of the most important and mostly used functions in MS SQL Server.

Let’s see the function syntax:

SUBSTRING(expression,start,length );

In this syntax:

• In this function there three parameters: (a) expression, (b) start, (c) length.
• Parameter expression is a character, binary, text, ntext, or image expression.
• Parameter start is an integer or bigint expression that specifies where the returned characters start. Here indexing start from 1, meaning that the first character in the expression is 1. If start is less than 1, the returned expression will begin at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start + length- 1 or 0. If start is greater than the number of characters in the value expression, a zero-length expression is returned be a constant, variable, or column.
• Parameter length is a positive integer or bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.
• Returns varchar  type data if specific expression is  type of char/varchar/text, returns nvarchar type data if specific expression is type of nchar/nvarchar/ntext and return varchar  type data if specific expression is  type of binary/varbinary/image.
• The values for start and length must be specified in number of characters for ntext, char, or varchar data types and bytes for text, image, binary, or varbinary data types.
• The expression must be varchar(max) or varbinary(max) when the start or length contains a value larger than 2147483647
• Function returns specific part of expression specified by the start and length.

The following example uses SUBSTRING function to create different parts of a name.

DECLARE @name varchar(30) = 'Minhajur Rahman Khan';
SELECT 
SUBSTRING(@name,1,8) AS first_name,
SUBSTRING(@name,10,6) AS middle_name,
SUBSTRING(@name,17,4) AS last_name; 

Here is the result set.

I have an another post MS SQL Server String Related Functions where you will learn about different string related functions of MS SQL Server.

In this tutorial, I have shown you how to use SUBSTRING function in MS SQL Server. Hope you have enjoyed the tutorial. If you want to get updated, like my facebook page http://www.facebook.com/freetechtrainer and stay connected.