SQL SUBSTRING Function

The SQL Server SUBSTRING is one of the String functions used to return the specified number of characters from the given expression. In real-time, while performing data analysis, it is common to extract a word, part of a string column. In this scenario, you can use the SQL Substring function to extract the part from the required string column.

The SQL Substring function will work on characters, images, text, and binary. The SUBSTRING function uses its third argument to decide, How many characters it should return.

In this article, we will explore the working functionality of the SQL Server SUBSTRING function, starting with syntax and basic variable examples. In the later section, we show how to use SUBSTRING on columns, extract text before and after, and find domain names from Email.

SQL SUBSTRING Function Syntax

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

SUBSTRING(String_Expression, Starting_Position, Length)

The parameters from the above SQL string substring function syntax is:

• String_Expression: It is from where you want to return or extract the characters. It can be an expression, a column name, or a string literal.
• Starting_Position: This parameter of the INT data type defines the starting position. It is the place where you have to decide from which index position you want the server to start selecting the characters. For example, 1 = from the first position, 2 = from 2nd character, etc. 
• Length: It is an optional argument. However, use this parameter to specify how many characters you want to extract from the Expression. 

NOTE: If you have not provided the length parameter value, the SQL SUBSTRING function returns all characters from the Starting_Position to the end. So, to avoid this, you have to mention the required number of characters using the length argument.

SQL Server Substring Function Examples

The following list of examples helps you understand the SQL Substring Function syntax and the numerous ways of utilizing this powerful function to extract data.

For this SQL SUBSTRING Function demo, we use the below data

SQL Substring From String literals

The following query extracts a part from a given string variable and returns the text. In this example, the first line of SQL Server code declares a string variable and assigns the ‘Learn Any Program’ text.

DECLARE @Expression varchar(50)
SET @Expression = 'Learn Any Program' 

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

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

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

In the first Select Statement, we specified the starting position as 1 and the length as 5. It means the SUBSTRING function will start at position 1 and extract five characters from that position.

SELECT SUBSTRING(@Expression, 1, 5) AS 'SUB' 

The next statement starts at index position 3 and returns 10 characters.

SELECT SUBSTRING(@Expression, 3, 10) AS 'SUB'

Next, we used the SQL Server substring function with the second argument as 20. Here, the original expression length is 17, and 20 is greater than the string length. So, the String Function will return all the characters from the @Character_Expression.

SELECT SUBSTRING(@Expression, 1, 20) AS 'SUB' 

In the following line, we assigned starting position as 20, greater than the original string length. So, it will return an Empty string because there is nothing to return at that position.

SELECT SUBSTRING(@Expression, 20, 25) AS 'SUB' 

Substring From Table Columns

The SQL Server Substring function also allows you to extract or select a required number of characters from the column values. In this example, we use this function to extract substring starting at index position 2 (starting position) and return nine characters from all the records inside the Department name string column. We have also mentioned another example line that starts at the 6th position and returns 17 characters from that position.

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

Substring on Multiple Columns

Sometimes, you may need to extract and return substrings from multiple table columns. The SQL Server SUBSTRING function allows you to apply it on multiple columns. You can also use this function in combination with CONCAT to join the two substrings and create new columns.

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 use a fixed value. In the following examples, we will show you how to use dynamic value as a second and third argument to return the domain names accurately.

SQL Server Substring Before Character – CHARINDEX Example

In some situations, you have to extract the text before a special character or empty space. In SQL Server, you can use a combination of SUBSTRING, CHARINDEX, and LEN or LEFT to extract a substring before a specific character.

The most common scenario is extracting text before the @ symbol from the Email ID. In this example, we will find the string before the Empty space in the Department Name column.

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

SQL Substring after Character to Find Domain Names in the Email

In this example, we find the string after the @ symbol in the Email Address column. It means we will find the domain names present in the Email column using SQL String Substring, CHARINDEX, and LEN Functions.

In this example, we use the CHARINDEX as the second to find the @ index position and the LEN function to find the column length for each row.

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

The below statement 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

The next line is finding the length of the Email using LEN Function.

LEN([Email])

Overall, the server will start at the index position after the @ symbol and End when the string ends.

SQL Substring Before and After Characters

This example is a combination of the above two examples. The first statement returns the characters before the @ symbol, and the second one returns the character after @ symbol of the Email ID.

In the first statement, the starting position is one, so it has to start from the beginning. Next, the CHARINDEX(‘@’, Email) helps to identify the position of the @ symbol, and the SQL SUBSTRING function returns characters up to @. If you forget to subtract one from it, the output includes @ symbol.

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

SQL Substring in Where Clause

We can also use this SQL Server SUBSTRING function inside a Where Clause to filter the SELECT statement output based on a few characters instead of a complete column. The below example returns all the records from the Employee table whose Department name ends with r. As this function is case-insensitive, it returns all the rows whose Department name ends with r or R. You can also try SUBSTRING([DepartmentName], -1) = ‘r’, and it will return the same.

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

SQL Substring in Subquery With LIKE Operator

Here, we used the SQL SUBSTRING function to extract a portion of text from SUBQUERY. The Like Operator within the subquery restricts the result to a few records. The below query will find the records whose Email ID ends with Hotmail.com.

SELECT SubQ.[FirstName]
      ,SubQ.[LastName]
      ,SubQ.[DepartmentName]
	  ,SUBSTRING(SubQ.[DepartmentName], 1, 8) AS SUB
	  ,SubQ.Email
FROM (
    SELECT * FROM [Employe]
    WHERE Email LIKE '%hotmail.com'
) AS SubQ;

SQL Substring in Order Clause

In this example, we used the SUBSTRING function inside an Order By Clause to sort the data using this result. In the below example, SUBSTRING(Email, 4, 5) will extract the text starting at index position 4 and count 5 characters from there. The ORDER BY Clause will sort the records using that extracted five characters.

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
	  ,SUBSTRING([DepartmentName], 2, 9) AS Dept
      ,[Email]
	  ,SUBSTRING(Email, 4, 5) AS [Text]
  FROM [Employe]
  ORDER BY SUBSTRING(Email, 4, 5)

SQL Server Substring with Negative Index Example

The SQL Server String Substring also allows you to use a negative Index, but it might not make sense. For instance, ([DepartmentName], -2, 9) 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 how to write the last 3 characters or 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 row 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)

SUBSTRING to Parse File Path or Raw Photos

This SQL Server SUBSTRING function can extract characters from images, text, file paths, and binary values in a table column.

SELECT [PhotoID]
      ,[PhotoSource]
      ,[Photo]
	  ,SUBSTRING([PhotoSource], 1, 1) AS Drive
	  ,SUBSTRING([Photo], 1, 8) AS ShortPhoto
  FROM [SSIS Tutorials].[dbo].[PHOTOSOURCE]

SQL Substring PATINDEX Example

In SQL Server, By combining the SUBSTRING function and PATINDEX function, you can extract a part of text using a regular expression or pattern. The PATINDEX function finds and returns the starting position of a given regex pattern within a string.

SELECT [CustomerKey],[FirstName],[LastName],[EmailAddress],
	SUBSTRING([EmailAddress], 1, 
			PATINDEX('%[0-9]%', [EmailAddress] COLLATE Latin1_General_BIN) - 1)
             AS TextBefore,
    SUBSTRING([EmailAddress],
			PATINDEX('%[0-9]%', [EmailAddress] COLLATE Latin1_General_BIN), 
			LEN([EmailAddress])) AS TextAfter
FROM [DimCustomer]