SQL SUBSTRING Function

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

SQL SUBSTRING Function Syntax

It will work on characters, images, text, and binary, and the syntax of the SQL SUBSTRING function is shown below.

SELECT SUBSTRING(Expression, Starting_Position, Length)
FROM [Source]

• Expression: Expression from which to want to return the characters.
• Starting_Position: From which index position do you want to start selecting the characters?
• Length: How many characters do you want to extract from the Expression?

For this demo, We use the below data

SQL Server Substring Function Example

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

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

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

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

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

The first line of SQL Server code is used to declare a string variable and assign the data. Next, 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 
SELECT SUBSTRING(@Expression, 1, 5) AS 'SQLSUBSTRING'

In the next line, We set the second argument as 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 Length
SELECT SUBSTRING(@Expression, 1, 20) AS 'SQLSUBSTRING' 

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

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

Substring Example 2

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

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
   ,SUBSTRING([DepartmentName], 2, 9) AS Dept
      ,[Email]
  FROM [Employe]

SQL Server Substring Before and After Example

Here, we used it on both the Department and Email columns.

• 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

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
   ,SUBSTRING([DepartmentName], 2, 9) AS Dept
      ,[Email]
   ,SUBSTRING(Email, 5, 5) AS [SQLSUBSTRING]
  FROM [Employe]

If you observe the above result, this string 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 Character

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

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
   ,SUBSTRING([DepartmentName], 
 CHARINDEX(' ', [DepartmentName]) + 1,
 LEN([DepartmentName])
 ) AS Dept
      ,[Email]
  FROM [Employe]

SQL Substring Find Domain Names in the Email?

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

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
   ,SUBSTRING(
 Email, 
 CHARINDEX('@', Email) + 1,
 LEN(Email)
 ) AS EmailString
  FROM [Employe]

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 the next position.

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

Finding the length of Email using LEN Function

LEN([Email])

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

SQL Substring Charindex

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

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
   ,SUBSTRING(
 Email, 
 1,
 CHARINDEX('@', Email) - 1
 ) AS EmailString
  FROM [Employe]

SQL Substring Like Operator

Here, we used the Like Operator to restrict the result to a few records. It will find the records whose Departname ends with Developer.

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
   ,SUBSTRING([DepartmentName], 2, 13) AS Dept
      ,[Email]
  FROM [Employe]
  WHERE DepartmentName LIKE '%Developer'

Substr Where Clause

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

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
  FROM [Employe]
  WHERE SUBSTRING([DepartmentName], 
 LEN([DepartmentName]), 1) = 'r'

SQL Substring in Order Clause

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

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)

Negative Index Example

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

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

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
   ,SUBSTRING([DepartmentName], -2, 9) AS Dept
   ,SUBSTRING([DepartmentName], 2, 9) AS O_Dept
      ,[Email]
  FROM [Employe]
 FROM [Employe]

Susbtr Negative Index Example 2

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

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
   ,SUBSTRING([DepartmentName], 
 LEN([DepartmentName]) + 1 - 5, 4) AS Dept
      ,[Email]
   ,SUBSTRING([Email], LEN(Email) - 2, 3) AS EmailString
  FROM [Employe]

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

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

=> (ab@hotmail.com, 14 – 2, 3) => (ab@hotmail.com, 12, 3)

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