Using SQL Server SUBSTRING Function

SUBSTRING() function is one of the most useful and popular string manipulation function of SQL Server. This function allows you to extract a specific substring or a portion of substring from a string. In this SQL Server Tutorial I will explain how to use the SUBSTRING function in SQL Server (Transact-SQL/TSQL) with syntax and examples.

SYNTAX:
The syntax for the SUBSTRING function in SQL Server is:

SUBSTRING( string, start position, length )

Arguments:
String: String or string column of a table.
Start Position: Is an integer or bigint that specifies where the returned character(s) start.
Length: Is a positive integer or bigint that specifies how many characters will be returned.

Note:
1. The first position of a string is always 1 not 0.
2. For negative start position the length will be (start position + length – 1).
3. Length value must be a positive integer or bigint. If length is negative, an error is generated and the statement is terminated.
4. You can provide any integer value for length. If the length is greater considering start position then the return value will be from start position to end of the string.

SUBSTRING FUNCTION EXAMPLE:
SQL Server SUBSTRING function

Example 1: To get the First character of a string:

SELECT SUBSTRING('ABCDEFGHI',1,1) 'Output'

Example 2: To get the Last character of a string:

SELECT SUBSTRING('ABCDEFGHI',LEN('ABCDEFGHI'),1) 'Output'

Example 3: To get a specific portion of a string:

SELECT SUBSTRING('ABCDEFGHI',2,3) 'Output'

Output:
“BCD”

Example 4: If the length is greater considering start position:

SELECT SUBSTRING('ABCDEFGHI',1,100) 'Output'

Output:
“ABCDEFGHI”

Example 5: Start position as a negative number:

SELECT SUBSTRING('ABCDEFGHI',-1,3) 'Output'

Output:
“A”

Summary:
SQL Server SUBSTRING string function 2

Posted in Database, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

     

*