SQL SUBSTRING Function

How to write SUBSTRING in SQL Server with an example?. The SQL Substring function a String Function used to return the specified number of characters from the given expression. The SQL Server Substring function uses its third argument to decide, How many characters it should return.

TIP: SQL substring Function will work on characters, images, text, and binary.

SQL SUBSTRING Function Syntax

The syntax of the SUBSTRING function in SQL Server is

SELECT SUBSTRING (Expression, Starting_Position, Length)
FROM [Source]
  • Expression: Expression from which to want to return the characters.
  • Starting_Position: From which index position, you want to start selecting the characters.
  • Length: How many characters you want the Substring to extract from the Expression.

For this Sql substring demo, We use the below data

SQL Substring Function 1

SQL Substring Function Example 1

The SQL Server Substring Function returns the specified number of characters from the given expression. The following Substring Function query extract a part from a given variable and returns the substring.

DECLARE @Expression varchar(50)
SET @Expression = 'Learn SQL Server' 

--Using Positive Integer 3 -- SQL
SELECT SUBSTRING (@Expression, 1, 5) AS 'SQL SUBSTRING' 

-- End Position is Greater than String Length
SELECT SUBSTRING (@Expression, 1, 20) AS 'SQL SUBSTRING' 

-- Starting Position is Greater than String Length
SELECT SUBSTRING (@Expression, 20, 25) AS 'SQL SUBSTRING' 
SQL Substring Function 2

Below lines of SQL Server code are used to declare a string variable and assigning the string data.

DECLARE @Expression varchar(50)
SET @Expression = 'Learn SQL Server'

We used the String substring in Sql Server to start at index position 1 and return five characters from the @Character_Expression variable.

--Using Positive Integer 3 -- SQL
SELECT SUBSTRING (@Expression, 1, 5) AS 'SQL SUBSTRING'

In the next line, We set the second argument like 20, which is greater than the string length. So, the String Function will return all the characters from the @Character_Expression

-- End Position is Greater than String Length
SELECT SUBSTRING (@Expression, 1, 20) AS 'SQL SUBSTRING' 

Here, We assigned starting position = 20, which is greater than the string length. So, the String Substring function will return Empty string because there is nothing to return at that position.

-- Starting Position is Greater than String Length
SELECT SUBSTRING (@Expression, 20, 25) AS 'SQL SUBSTRING'

SQL Substring Function Example 2

The SQL Server Substring function also allows you to select a required number of characters from the column values. In this example, substring starts at the index position 2 (starting position) and return nine characters from all the records present inside the Department name column.

-- SQL Server Substring Example
USE [SQL Tutorial]
GO
SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
   ,SUBSTRING([DepartmentName], 2, 9) AS Dept
      ,[Email]
  FROM [Employe]
SQL Substring Function 3

Substring before and after Example

Here, we used the SQL Server Substring function on both the Department column and the Email column.

  • Department Column: Starts at Second Position and returns 9 characters (including spaces)
  • Email Column: Start at the index position 5 and return five characters (including special characters) from the Email column
-- SQL Server Substring Example
SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
   ,SUBSTRING([DepartmentName], 2, 9) AS Dept
      ,[Email]
   ,SUBSTRING(Email, 5, 5) AS [SQL SUBSTRING]
  FROM [Employe]
SQL Substring Function 4

If you observe the above result, the Substring Function is not getting the domain names perfectly because we are using a fixed value. In the next example, we will show you how to use dynamic value as a second and third argument to return the domain names accurately.

SQL Substring after Charcater

In this substring example, we are using Charindex, and Len functions as the second and third arguments to return the characters after an empty space

-- SQL Server Substring Example
SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
   ,SUBSTRING([DepartmentName], 
 CHARINDEX(' ', [DepartmentName]) + 1,
 LEN([DepartmentName])
 ) AS Dept
      ,[Email]
  FROM [Employe]
SQL Substring Function 12

Find Domain Names in Email using SQL Substring Charindex

In this example, We are going to find the domain names present in the Email column using String Substring Function.

-- SQL Server Substring Example
SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
   ,SUBSTRING(
 Email, 
 CHARINDEX('@', Email) + 1,
 LEN(Email)
 ) AS EmailString
  FROM [Employe]
SQL Substring Function 5

It will return the index position of the @ symbol. It means CHARINDEX Function will check for the index position of @ symbol in each and every record. Next, We added 1 to the index position because the domain name starts from next position.

CHARINDEX ('@', [Email]) + 1

Finding the length of Email using LEN Function

LEN([Email])

On the whole, Substring will start at the index position after the @ symbol and Ends when the string ends.

SQL Substring Charindex

In this substring example, We are going to find the string before the @ symbol in the Email Address column. For this, we are using the substring along with CHARINDEX

-- SQL Server SUBSTRING Example
SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
   ,SUBSTRING(
 Email, 
 1,
 CHARINDEX('@', Email) - 1
 ) AS EmailString
  FROM [Employe]
SQL Substring Function 6

SQL Substring Like Operator

Here, we used the Like Operator to restrict the Substring function result few records. It will find the substring of all the records whose Departname ends with Developer.

-- SQL Server SUBSTRING Example
SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
   ,SUBSTRING([DepartmentName], 2, 13) AS Dept
      ,[Email]
  FROM [Employe]
  WHERE DepartmentName LIKE '%Developer'
SQL Substring Function 7

SQL Substring in Where Clause

We can also use this Substring function inside a Where Clause. It returns all the records from the Employee table whose Department name ends with r.

-- SQL Server SUBSTRING Example
SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
  FROM [Employe]
  WHERE SUBSTRING([DepartmentName], 
 LEN([DepartmentName]), 1) = 'r'
SQL Substring Function 8

SQL Substring in Order Clause

In this example, we used the string Substring function inside an Order By Clause to sort the data using this substring result. The below statement will sort the records using the substring of Email starts at index position 4 and counts 5 characters from there.

-- SUBSTRING Example
SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
   ,SUBSTRING([DepartmentName], 2, 9) AS Dept
      ,[Email]
   ,SUBSTRING(Email, 4, 5) AS [Email Substring]
  FROM [Employe]
  ORDER BY SUBSTRING(Email, 4, 5)
SQL Substring Function 9

Substring Negative Index Example 1

The SQL Server String Substring also allows you to use a negative Index, but it might not make any sense.

Substring (-2, 9) => Starts at index position -2 and return 9 characters from that position. As we don’t have any records from -2 to 0 (3 records) so, the function will return characters from 1 to 6 (9 – 3)

-- Substring in SQL Server Example
SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
   ,SUBSTRING([DepartmentName], -2, 9) AS Dept
   ,SUBSTRING([DepartmentName], 2, 9) AS O_Dept
      ,[Email]
  FROM [Employe]
 FROM [Employe]
SQL Substring Function 10

SQL Substring Negative Index Example 2

This Substring Negative index example helps you understand the process of writing the last 3 characters, or the last four characters, etc.

-- Substring in SQL Server Example
SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
   ,SUBSTRING([DepartmentName], 
 LEN([DepartmentName]) + 1 - 5, 4) AS Dept
      ,[Email]
   ,SUBSTRING([Email], LEN(Email) - 2, 3) AS EmailString
  FROM [Employe]
SQL Substring Function 11

We will take one record, i.e., the first record from the Email column to explain the output.

SUBSTRING([Email], LEN(Email) – 2, 3)

SUBSTRING([email protected], 17 – 2, 3) => SUBSTRING([email protected], 15, 3)

So, the function will satrt at index psotion 15 and returns three characters from there. (com)