How to Extract a Substring From a String in T-SQL

Problem:

You would like to extract substrings from a text column in SQL Server.

Example 1:

In the emails table, there is an email column. You'd like to display the first seven characters of each email.

The table looks like this:

Solution 1:

SELECT
  email,
  SUBSTRING(email, 1, 7) AS substring
FROM emails;

The result is:

Discussion:

Use the SUBSTRING() function. The first argument is the string or the column name. The second argument is the index of the character at which the substring should begin. The third argument is the length of the substring.

Watch out! Unlike in some other programming languages, in T-SQL the indexes start at 1, not 0. This means the first character has index 1, the second character has index 2, etc.

SUBSTRING(email, 1, 7) will return the substrings of the values in the email column that start at the first character and go for seven characters.

Example 2:

You'd like to display the substring between indexes 2 and 6 (inclusive).

Solution 2:

SELECT
  email,
  SUBSTRING(email, 2, 5) AS substring
FROM emails;

The result is:

Discussion:

You use the SUBSTRING() function just as in the previous examples. This time, the second argument of the function is 2, since we want to start at index 2. The length of the substring is 5 (end_index - start_index + 1).

Example 3:

You'd like to display the substring that starts at the @ sign and ends at the end of the string, but you don't know the exact indexes or lengths.

Solution 3:

SELECT
  email,
  SUBSTRING(email, CHARINDEX('@', email), LEN(email) - CHARINDEX('@', email) + 1) AS substring
FROM emails;

The result is:

Discussion:

You use the SUBSTRING() function just as in the previous examples. This time, you're looking for a specific character whose position can vary from row to row. To find the index of the specific character, you can use the CHARINDEX(character, column) function where character is the specific character at which you'd like to start the substring (here, @). The argument column is the column from which you'd like to retrieve the substring; it can also be a literal string.

The third argument of the SUBSTRING() function is the length of the substring. You can calculate it using the CHARINDEX() and the LEN() functions. You do this by subtracting the index from the column length then adding 1:

LEN(email) - CHARINDEX('@', email) + 1

You may also want to retrieve a substring that doesn't end at the end of the string but at some specific character, e.g., before .. Here's how you can do this:

SELECT
  email,
  SUBSTRING(email, CHARINDEX('@', email), CHARINDEX('.', email) - CHARINDEX('@', email)) AS substring
FROM emails;

The result of this query is:

The part CHARINDEX('.', email) - CHARINDEX('@', email) simply calculates the length of the substring.